SSAS FAQ MDX FAQ How do I check for division by zero and null to avoid -1.#INF in cells
How do I check for division by zero and null to avoid -1.#INF in cells
User Rating: / 0
PoorBest 
Written by Thomas Ivarsson   
Monday, 26 May 2008 00:35

How do I check for division by zero and null to avoid -1.#INF in cells.

WITH
MEMBER [Measures].[ZeroValuePerUnit] AS 0
MEMBER [Measures].[DivideByZero] As [Measures].[Internet Sales Amount]/[Measures].[ZeroValuePerUnit] ,Format_String = '#.#0'
MEMBER [Measures].[CheckDivByZero] AS IIF([Measures].[ZeroValuePerUnit] = 0, Null,  [Measures].[Internet Sales Amount]/[Measures].[ZeroValuePerUnit]),Format_String = '#.#0'
SELECT {[Measures].[Internet Sales Amount], [Measures].[ZeroValuePerUnit], [Measures].[DivideByZero], [Measures].[CheckDivByZero]} ON COLUMNS,
 [Customer].[Country].Children ON ROWS
FROM [Adventure Works]
WHERE([Date].[Calendar].[Calendar Year].&[2004]);

Result will be:

 Internet Sales AmountZeroValuePerUnitDivideByZeroCheckDivByZero
Australia$2,563,884.29 01.#INF(null)
Canada$673,628.21 01.#INF(null)
France$922,179.04 01.#INF(null)
Germany$1,076,890.77 01.#INF(null)
United Kingdom$1,210,286.27 01.#INF(null)
United States$3,324,031.16 01.#INF(null)

 

< Prev   Next >
 
Comments (1)
1 Wednesday, 26 November 2008 10:48
Jonathan B
Another ides is to mathematics to solve the problem; the query parser will not carry out a formula in a multiplication is the first operand is NULL or 0 so we can multiply and divide out and that will be much quick than IIF (see Mosha for more details).

e.g.
MEMBER [Measures].[CheckDivByZero] AS
[Measures].[ZeroValuePerUnit] * (
[Measures].[Internet Sales Amount]/[Measures].[ZeroValuePerUnit])
) / [Measures].[ZeroValuePerUnit]
,Format_String = '#.#0'
Strategy Companion