Handling Division by ZERO errors in MDX Calculations

16751
81322

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

The result of the above given query is 1.#INF as shown in the below pic.

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

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.

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

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 –

That’s it and Happy Coding Folks !

Regards,

Roopesh Babu V

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

2. Really lots of useful tips!

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.

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] без комисси и по выгодной цене!