having 'Measure' > 0 syntax does not work in mdx query

Question

Hope you're doing well I've written an MDX query as you can see below :

with member [Measures].[TOTAL] AS
SUM(Periodstodate([DimTime].[Year].[(All)] ,[DimTime].[Year].currentmember)
,[Measures].[IndentCount])

Select {[Measures].[IndentCount],[Measures].[TOTAL]}  
   having [Measures].[IndentCount] > 0 on 0 , 
   non empty [DimTime].[Year].[Year] on 1
from [Procurement]

The problem is despite using having [Measures].[IndentCount] > 0 I still see the null value for my measure as you can see below :

. I want to know what the problem would be??? I have this problem even when I use Filter function

Thanks in advance

Answer 1

You put the HAVING clause in the ON COLUMNS axis (or ON 0) which attempts to filter out any column which has a null or zero IndentCount. Instead you need to move it to the ON ROWS axis (or ON 1) if you want it eliminate some rows.

I would also suggest you may optimize the performance by using the NonEmpty function instead of HAVING. Since you can’t have a zero or negative IndentCount the following will do what you want and will perform better since it will only calculate the TOTAL measure for rows you will end up keeping.

with member [Measures].[TOTAL] AS
SUM(Periodstodate([DimTime].[Year].[(All)] ,[DimTime].[Year].currentmember)
,[Measures].[IndentCount])

Select {[Measures].[IndentCount],[Measures].[TOTAL]}  
   on 0 , 
   NonEmpty([DimTime].[Year].[Year], [Measures].[IndentCount]) on 1
from [Procurement]