Answered by:
Nested IIF Statement
Question

I have a need to perform essentially a "double check" for a zero or NULL condition in an MDX statement, and was looking for some guidance on nested IIF statements  also on the ISEMPTY function.
Essentially the query is: [Actual Fees] / ([Amount Recognized] / [Estimated Fees])  [Estimated Fees]
In the SSAS cube, both Amount Recognized and Estimated Fees can either be zero or NULL. As you can see, if either of those conditions applies, a "divide by zero" error will be thrown. If Actual Fees is zero, it's no issue  it will just evelauate to zero.
My problem is how do I alter my MDX to handle the two possible conditions? If Estimated Fees is zero, then the expression Amount Recognized/Extimated Fees throws the error, which in turn will cause the whole calc to fail with can't divide by zero.
If Amount Recognized is zero, again the expression evaluates to zero, which will then cause the entire expression to error out.
Ultimately, my end goal is if the denominator is going to evaluate to zero, then I want the entire expression to just display zero.
A screen shot of the results right now:
And my MDX:
([Measures].[Actual Line Total]) / (([Measures].[Recognized Amount])/([Measures].[Estimate Line Total]))  ([Measures].[Estimate Line Total])
Thanks in advance!!
A. M. Robinson
Answers

Try this (you can use OR instead of nesting IIFs?):
IIF(
IsEmpty([Measures].[Recognized Amount])
OR
[Measures].[Recognized Amount]=0
OR
IsEmpty([Measures].[Estimate Line Total])
OR
[Measures].[Estimate Line Total]=0,
0,
([Measures].[Actual Line Total]) /
(
([Measures].[Recognized Amount])/
([Measures].[Estimate Line Total])
)  ([Measures].[Estimate Line Total])
)
Jegan  SQL Server BI Consultant (http://EasyBI.wordpress.com)
All replies

Try this (you can use OR instead of nesting IIFs?):
IIF(
IsEmpty([Measures].[Recognized Amount])
OR
[Measures].[Recognized Amount]=0
OR
IsEmpty([Measures].[Estimate Line Total])
OR
[Measures].[Estimate Line Total]=0,
0,
([Measures].[Actual Line Total]) /
(
([Measures].[Recognized Amount])/
([Measures].[Estimate Line Total])
)  ([Measures].[Estimate Line Total])
)
Jegan  SQL Server BI Consultant (http://EasyBI.wordpress.com)

iif([Measures].[Recognized Amount]+0=0 , 0 , iif((([Measures].[Recognized Amount])/([Measures].[Estimate Line Total]))+0=0 , 0 , (([Measures].[Actual Line Total]) / (([Measures].[Recognized Amount])/([Measures].[Estimate Line Total])))  ([Measures].[Estimate Line Total]) ) )
Hi ansonee ,
Hope the next code will help you :)
Regards, David .

David / Jegan:
Thank you! Both methods worked equally well...I am seeing some weird results though.
As you can see, Compionent_ID 96194 ans 96192 are WAY off!! I have no idea why those numbers are as off as they are. This seems to be occuring sporadically throughout the cube.
Any ideas what could be the underlying caude?
A. M. Robinson

My guess is the Recognized Amount is showing as 0 but internally represented as a really small float number.
Can you try rounding [Recognized Amount] to the nearest integer to see if this is the problem.
IIF(
IsEmpty([Measures].[Recognized Amount])
OR
ROUND([Measures].[Recognized Amount])=0
OR
IsEmpty([Measures].[Estimate Line Total])
OR
[Measures].[Estimate Line Total]=0,
0,
([Measures].[Actual Line Total]) /
(
ROUND([Measures].[Recognized Amount])/
([Measures].[Estimate Line Total])
)  ([Measures].[Estimate Line Total])
)Jegan  SQL Server BI Consultant (http://EasyBI.wordpress.com)
