Handling Division by ZERO errors in MDX Calculations

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

 

2 COMMENTS

  1. Just how to understand whether you’re doing every little thing properly? In doing so, you will better have the ability to obtain into the heart of these enchanting Chinese ladies like you discover on your own with a Western woman. A lovely Chinese lady is somebody that will certainly look after you. Be accountable: when discussing being responsible, you require to confirm that you’re an individual who can look after your Chinese spouse. When considering Chinese lady dating suggestions, being charming is one of the most efficient ones. Take notice of how you choose a Chinese dating site to stay clear of being scammed, so don’t allow any type of poor quality sites interfere in your joy. Solitary men like Oriental women likewise such as to use these websites since most of them are looking for love. Single Chinese ladies are not as unusual as you might believe. In truth, there are hundreds of thousands of solitary Chinese ladies in China today.

    When looking for love in China is on the internet chatting and net video gaming, the next vital aspect of Chinese culture that you should understand. China females dating is quite appealing for western males, thanks to the mystery which borders these ladies. The only point you need is to find a trustworthy platform where you can locate Chinese women for dating and also marital relationship. Allow’s see what various other reasons you have for the marriage with Philipinos:. Well, allow’s simply state that a lot of males have prospered at repelling Chinese females for marriage more than at attracting them with their message messages. Although Chinese females aren’t meaningful, they’ll seek much more possibilities to spend time with you. Chinese ladies you’ll be dating will do their finest to obtain suched as by you and also hence, they’ll do every little thing for you. In order to obtain an excellent companion forever, it is essential that you are able to win over these individuals.

    Chinese ladies who have loved these brand-new individuals in Beijing. Nevertheless, they are trustworthy, reliable partners that are flexible sufficient to adjust their habits according to the situation. Because of this, most of these Chinese women agree to share their pasts with you. Prepare to meet her family members: family members approval is a must in typical Chinese households. All Chinese family members have a background that is vibrant as well as really rich. One thing that you need to understand about Chinese ladies is that they are not truly searching for a man who is rich or has cash. It is therefore extremely crucial [url=https://www.bestbrides.net/what-to-expect-when-marrying-a-filipina/]what to expect when marrying a filipina[/url] that you, as a man fascinated in dating Chinese women, understand their idea of romance. Do not enable your woman to foot the bill, as in their dating etiquette, a guy is accountable for that. Nevertheless, they additionally understand it as well as choose when males focus on them not only as a result of their external charm. Nonetheless, utilizing these systems is still way a lot more affordable than going all the way to the Philippines. With the progression of social networks and also online dating, men have much more accessibility to hot Chinese girls, so their attempts to find effective pick-up lines have magnified.

    Males have been searching for the most tempting opening lines – also known as pick-up lines – to make use of on Chinese girls considering that the dawn of time. No, they just seek significant relationships with actual guys. Chinese ladies for dating are dissimilar to Western ladies, so social differences could become defining in your connections. Knowing some dating Chinese ladies tips might do you a favor. It is really vital to know just how to talk with Chinese women online. There are a variety of games and also chats which are preferred amongst Chinese girls and they such as to chat with different people online. The Chinese audience of the most effective dating sites is primarily from large cities like Beijing. There’s additionally a substantial amount of dating sites where you can find a Filipina mail order bride-to-be without any type of issues. Consequently, you can feel confident of grooming cultured kids with a Russian bride-to-be. Russian ladies boast a distinct combination of Slavic as well as Oriental genes, which has actually generated a few of one of the most gorgeous females in the world. That is why a lot of Filipino ladies relocate to other cities. There are definitely a lot more reasons why you need to date a Filipino woman, however the ten points discussed above are one of the most special qualities that make them stand out from the remainder.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

62 − = 56