One of the most common things to need an IF statement for in MDX is to determine if a measure is NULL to ensure that you output Null instead of the actual calculation.
Mosha pioneered an interesting “trick” for avoiding the use of “IF” to account for Null handling in Budget Variance (See http://sqlblog.com/blogs/mosha/archive/2006/11/05/budget-variance-a-study-of-mdx-optimizations-evaluation-modes-and-non-empty-behavior.aspx)
Basically we can abuse the fact that in MDX Null multiplied by any other number is null.
Having used Mosha’s technique for Budget Variance, I realised that this can pretty much be used anywhere, so below I have a calculated member for YTD
SCOPE ([Reporting].[Period].[YTD]);
this = iif([Reporting].[Period].[This Period]=null,null,
Aggregate( [Reporting].[Period].[This Period]
*
PeriodsToDate( [Date].[Calendar].[Year],
[Date].[Calendar].CurrentMember
)));
END SCOPE;
We can re-write this without the IF as below and enjoy faster performance:
SCOPE ([Reporting].[Period].[YTD]);
this = [Reporting].[Period].[This Period] *
Aggregate( [Reporting].[Period].[This Period]
*
PeriodsToDate( [Date].[Calendar].[Year],
[Date].[Calendar].CurrentMember
)) / [Reporting].[Period].[This Period];
END SCOPE;
END SCOPE;
