# MDX:Handling division by zero errors

WITH
MEMBER [Date].[Calendar Year].[2006VS2007] AS
[Date].[Calendar].[Calendar Year].&[2007] /
[Date].[Calendar].[Calendar Year].&[2006],
FORMAT_STRING = 'Percent'
SELECT
{ [Date].[Calendar Year].[Calendar Year].&[2006],
[Date].[Calendar Year].[Calendar Year].&[2007],
[Date].[Calendar Year].[2006VS2007]  } *
[Measures].[Internet Sales Amount] ON 0,

non empty {([Product].[Product Categories].children
)
}
ON 1
FROM

If you notice where Value of Internet Sales is Null for Base Year in our case CY 2006 the result is 1.#INF

In our case for Accessories and Clothing we are getting result as 1.#INF because of simple reasons 1/0 is 1.#INF

1.#INF – is nothing but formatted value of infinity

Another way of checking result of ‘1/0’ is given below

with  member [Measures].x
as 1/0
select
[Measures].x on 0

As expected result if infinity.

We can workaround this behavior by adding IIF statement, where we are stating if denominator is Zero then show NULL else perform Division.

WITH
MEMBER [Date].[Calendar Year].[2006VS2007] AS
iif([Date].[Calendar Year].[Calendar Year].&[2006]=0,NULL,
[Date].[Calendar].[Calendar Year].&[2007] /
[Date].[Calendar].[Calendar Year].&[2006]),
FORMAT_STRING = 'Percent'
SELECT
{ [Date].[Calendar Year].[Calendar Year].&[2006],
[Date].[Calendar Year].[Calendar Year].&[2007],
[Date].[Calendar Year].[2006VS2007]  } *
[Measures].[Internet Sales Amount] ON 0,

non empty {([Product].[Product Categories].children
)
}
ON 1
FROM