Handling division by zero errors

Start a new query in SQL Server Management Studio and check that you're working on the Adventure Works database. Then write and execute this query:

WITH 
MEMBER [Date].[Calendar Year].[CY 2012 vs 2011 Bad] AS 
   [Date].[Calendar Year].[Calendar Year].&[2012] / 
   [Date].[Calendar Year].[Calendar Year].&[2011], 
   FORMAT_STRING = 'Percent' 
SELECT 
   { [Date].[Calendar Year].[Calendar Year].&[2012], 
     [Date].[Calendar Year].[Calendar Year].&[2011], 
     [Date].[Calendar Year].[CY 2012 vs 2011 Bad] } * 
     [Measures].[Reseller Sales Amount] ON 0, 
   { [Sales Territory].[Sales Territory].[Country].MEMBERS } 
   ON 1 
FROM 
   [Adventure Works] 

This query returns six countries on the rows axis, and two years and a ratio on the column axis:

The problem is that we get 1.#INF on some ratio cells. 1.#INF is the formatted value of infinity, and it appears whenever the denominator CY 2011 is null and the nominator CY 2012 is not null.

We will need help from the IIF() function, which takes three arguments: IFF(<condition>, <then branch>, <else branch>). The IIF() function is a Visual Basic for Applications (VBA) function and has a native implementation in MDX. The IIF() function will allow us to evaluate the condition of CY 2011, then decide what the ratio calculation formula should be.

Follow these steps to handle division by zero errors:

The result shows that the new calculated measure has corrected the problem. The last column [CY 2012 vs 2011 Good] is now showing (null) correctly when the denominator CY 2011 is null and the nominator CY 2012 is not null.

A division by zero error occurs when the denominator is null or zero and the numerator is not null. In order to prevent this error, we must test the denominator before the division and handle the two scenarios in the two branches using the IIF() statement.

In the condition part of the IIF statement, we've used a simple scalar number zero to determine whether [Measures].[Reseller Sales Amount] in the following slicer is zero or not. If it is zero, then it will be true and the calculated member will be NULL:

[Date].[Calendar Year].[Calendar Year].&[2011] = 0 

What about the NULL condition? It turned out for a numerical value; we do not need to test the NULL condition specifically. It is enough to test just for zero because null = 0 returns true. However, we could test for a NULL condition if we want to, by using the IsEmpty() function.

For the calculated member, [CY 2012 vs 2011 Good] we could wrap the member with the IsEmpty() function. The result will be the same:

MEMBER [Date].[Calendar Year].[CY 2012 vs 2011 Good] AS 
   IIF(IsEmpty([Date].[Calendar Year].[Calendar Year].&[2011]), 
       null, 
       [Date].[Calendar Year].[Calendar Year].&[2012] / 
       [Date].[Calendar Year].[Calendar Year].&[2011] 
      ), 
   FORMAT_STRING = 'Percent' 

SQLCAT's SQL Server 2008 Analysis Services Performance Guide has a lot of interesting details regarding the IIF() function, found at http://tinyurl.com/PerfGuide2008R2 .

Additionally, you may find the blog article MDX and DAX topics by Jeffrey Wang, explaining the details of the IIF() function, found at http://tinyurl.com/IIFJeffrey .