Friends,
Handling Divided by Zero errors are very important in MDX. In other words we can call it as Divided by NULL errors in MDX. The error that show when you try to divide by Zero/Null is “1.#INF”. Lets see a small example.
If you see the below given query, it consists of a calculation which divided 1 with 0.
with member [Measures].[DivideByZeroError]
as 1/0
SELECT [Measures].[DivideByZeroError] on COLUMNS
from [Adventure Works DW]
The result of the above given query is 1.#INF as shown in the below pic.
Now I have written a calculation to check (Sales Amount)/(Discount Amount) for all products and the query looks like –
with member [Measures].[Sales to Discount]
as [Measures].[Sales Amount]/[Measures].[Discount Amount]
SELECT {[Measures].[Sales Amount],
[Measures].[Discount Amount],
[Measures].[Sales to Discount]} on COLUMNS ,
[Dim Product].[English Product Name].[English Product Name] on ROWS
from [Adventure Works DW]
IF you run the above query you can see many values coming as “1.#INF” as Discount Amount is either NULL or 0 for many products and PFB Screenshot of the same.
Lets see how can we make use of IIF condition to avoid these errors. The IIF Condition syntax in MDX is as given below.
IIF(condition,true,false) – If the condition is TRUE then first part will be executed else second provided in the iff condition. The query looks like below after applying IIF condition.
with member [Measures].[Sales to Discount]
as iif([Measures].[Discount Amount] = 0,null,[Measures].[Sales Amount]/[Measures].[Discount Amount])
SELECT {[Measures].[Sales Amount],
[Measures].[Discount Amount],
[Measures].[Sales to Discount]} on COLUMNS ,
[Dim Product].[English Product Name].[English Product Name] on ROWS
from [Adventure Works DW]
In the above query I am specifying that if Discount Amount is 0 then place NULL for the product in the calculated measure output else calculate the value. The output will look like this –
That’s it and Happy Coding Folks !
Regards,
Roopesh Babu V
