1

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 :

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

Thanks in advance

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]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.