Handling Division by ZERO errors in MDX Calculations

By
roopesh.valluru
-
8591
47776
Divided by Zero/ Null
Divided by Zero/ Null

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.

Output of the calculation which divides 1 by 0
Output of the calculation which divides 1 by 0

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.

Divide By Zero Error
Divide By Zero Error

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 –

IIF Condition
IIF Condition

That’s it and Happy Coding Folks !

Regards,

Roopesh Babu V

 

RELATED ARTICLESMORE FROM AUTHOR

8591 COMMENTS

  10. Собственное производство, доставка и монтаж под ключ [url=https://karkasnik-pod-kluch.ru/]каркасный дом[/url] гарантия на готовую конструкцию, доставка 500 км за наш счет.

  23. Really lots of useful tips!
    [url=https://payforanessaysonline.com/]college essay for sale[/url] essays online to buy [url=https://buycheapessaysonline.com/]custom essays for sale[/url] buy a custom essay

  29. Terrific info, Regards.
    [url=https://studentessaywriting.com/]essay writing techniques[/url] uk based essay writing services [url=https://essaywritingserviceahrefs.com/]unique essay writing service[/url] same day essay writing service

  35. Nicely put, Thanks!
    [url=https://essaypromaster.com/]websites that write papers for you[/url] ai essay writer [url=https://paperwritingservicecheap.com/]write my sociology paper[/url] someone write my research paper

  41. You said it nicely.!
    [url=https://essaywritingservicelinked.com/]uk coursework writing service[/url] writing an analytical essay [url=https://essaywritingservicetop.com/]cheap paper writing service[/url] top 10 essay writing services uk

  46. Thanks a lot. Lots of info!
    [url=https://ouressays.com/]custom term paper writing[/url] graduate thesis proposal [url=https://researchpaperwriterservices.com/]proposal master[/url] help with dissertation proposal

  51. This is nicely expressed. !
    [url=https://phdthesisdissertation.com/]dissertation writing services cost[/url] dissertation help co uk [url=https://writeadissertation.com/]writing service proposal[/url] research proposal phd application

  56. Point clearly utilized..
    [url=https://quality-essays.com/]pay for writing papers[/url] pay for college essays [url=https://buyanessayscheaponline.com/]essay order[/url] cheap essays to buy

  61. Nicely put, Thanks.
    [url=https://payforanessaysonline.com/]buy an essay paper online[/url] where can i buy essays [url=https://buycheapessaysonline.com/]pay for writing essay[/url] where can i buy an essay

  64. This is nicely put! .
    [url=https://essaytyperhelp.com/]need help in writing an essay[/url] essays writing help [url=https://helptowriteanessay.com/]writing help[/url] essay introduction help

  73. Appreciate it! Numerous info.
    [url=https://writingpaperforme.com/]write a paper for me[/url] how to write a science paper [url=https://custompaperwritersservices.com/]write my research paper[/url] federalist papers writers

  76. Incredible a good deal of great data.
    [url=https://ouressays.com/]research papers buy[/url] need help writing a research paper [url=https://researchpaperwriterservices.com/]proposal essay[/url] proposal headings

  219. Lovely data, Kudos!
    [url=https://writinganessaycollegeservice.com/]custom thesis writing service[/url] linkedin profile writing service uk [url=https://essayservicehelp.com/]essay writing service usa[/url] how to advertise resume writing service

  527. Здесь можно [url=https://vc.ru/crypto/652334-kupit-bitkoin-v-2023-godu-kak-i-gde-bezopasno]купить биткоин[/url] без комисси и по выгодной цене!