MDX - SQL Server Analysis Services 2012 Cube Development - page 216

WITH MEMBER [measures].[average unit
price] AS
[measures].[reseller unit price] /
measures.[reseller transaction count],
FORMAT_STRING="Currency"
2. At first glance this calculation looks good, but it includes a division operation,
which will result in an error if the transaction count is 0. We will wrap the cal-
culation in the IIF function and display N/A for "Not Available" in case the
transaction count is indeed zero.
WITH MEMBER [measures].[average unit
price] AS IIF (measures.[reseller
transaction count]=0, "N/A",
[measures].[reseller unit price] /
measures.[reseller transaction count]),
FORMAT_STRING="Currency"
3. Lastly, you can specify a performance hint using the NON_EMPTY_BEHAVIOR
property, which advises Analysis Services to consider the calculated meas-
ure empty if the underlying measure is empty. As discussed earlier, large
cubes can have many empty cells. Traversing the huge cube space and
applying the division to each cell individually can be slow; the
NON_EMPTY_BEHAVIOR property helps SSAS eliminate empty cells from
consideration. The final calculation along with the full query will look like this:
WITH MEMBER [measures].[average unit
price] AS IIF (measures.[reseller
transaction count]=0, "N/A",
[measures].[reseller unit price] /
measures.[reseller transaction count]),
FORMAT_STRING="Currency",
NON_EMPTY_BEHAVIOR=measures.[reseller
unit price]
SELECT [Geography].[Country].[All
Geographies].Children ON 0,
[Measures].[average unit price] ON 1