Adding Calculations to the Cube - Expert Cube Development with SSAS Multidimensional Models - page 150

The function to use to make this check is IIF . It takes three parameters and if the first
evaluates to True , it returns the value of the second parameter, otherwise it returns
the third one. We can now rephrase the ratio as:

CREATE MEMBER CURRENTCUBE.Measures.RatioOverCountry AS
IIF (
(
[Measures].[Sales Amount],
Ancestor (
[Customer].[Customer Geography].CurrentMember,
[Customer].[Customer Geography].Country
)
) = 0,
NULL,
([Measures].[Sales Amount]) /
(
[Measures].[Sales Amount],
Ancestor (
[Customer].[Customer Geography].CurrentMember,
[Customer].[Customer Geography].Country
)
)
),
FORMAT_STRING = "Percent";

You might be wondering: what happens if we try to divide by NULL
instead of zero? In fact the same thing happens in both cases, but the
preceding code traps division by NULL as well as division by zero
because in MDX, the expression 0=NULL evaluates to true . This
behavior can come as a bit of a shock, especially to those people with
a background in SQL, but in MDX, it causes no practical problems and
is actually quite helpful.

Even if this formula is correct, it still has two problems:

• It is not very easy to understand because of the repetition of the formula

to get the sales at the Country level, first in the division-by-zero check and
second in the ratio calculation itself.

• The value of the sales at the Country level will not be cached and so it

will lead to a double computation of the same value. Analysis Services
can only cache the value of an entire calculation, but can't cache the results
of expressions used inside a calculation.