MDX Help

# MDX Help

• Tuesday, February 19, 2013 1:24 AM

I've the following 4 measures:
[Measures].[Val]
[MEASURES].[Cost]
[MEASURES].[Sales]
[MEASURES].[Profit]

The values in these could be: a numeric/decimal value, NULL or Zero.

I need help to create a new calculated measure which returns the first non zero/non null by considering the above order.

I used a COALESCEEMPTY - which works as long as one of the measure doesn't have the value = 0. The measure that could have a zero value is: [Measures].[Val]

GBM

### All Replies

• Tuesday, February 19, 2013 3:40 AM

If I understood you correctly, the following probably is what you want:

IIF ( [Measures].[Val]<>0 and [Measures].[Val] is not null, [Measures].[Val],

IIF ( [MEASURES].[Cost]<>0 and [MEASURES].[Cost] is not null, [MEASURES].[Cost],

IIF ( [MEASURES].[Sales]<>0 and [MEASURES].[Sales] is not null, [MEASURES].[Sales],

IIF ( [MEASURES].[Profit]<>0 and [MEASURES].[Profit] is not null, [MEASURES].[Profit],0))

note that if all the measures = to 0 or null then the above will return 0, as you didn't mention what to do when all of them = 0 or null

• Edited by Tuesday, February 19, 2013 3:41 AM
• Marked As Answer by Tuesday, February 19, 2013 9:41 PM
•
• Tuesday, February 19, 2013 3:52 PM

if it is possible and would not change the meaning of your data you may also replace your 0-values by NULL in your relational table/view

then set NullProcessing-Property of your measures to "Preserve" and you can use COALESCEEMPTY() as you initially intended to

hth,
gerhard

- www.pmOne.com -

• Tuesday, February 19, 2013 9:42 PM

Thanks Gerhard for your suggestion. I already have the NULL values in the underlying fact table, but for some odd reason the SSAS converts those into zeros - any idea why that would be?

GBM

• Wednesday, February 20, 2013 2:07 PM