Handling Division by ZERO errors in MDX Calculations

16267
77601
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

 

16267 COMMENTS

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

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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

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