Handling Division by ZERO errors in MDX Calculations

By
roopesh.valluru
-
28
85243
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

28 COMMENTS

  5. CCTV Focus provides in-depth analysis of video surveillance technologies, covering IP camera systems and software solutions. It features detailed reviews of video management software (VMS), highlighting features such as object detection using artificial intelligence. The site offers ratings of surveillance system manufacturers and their products. A curated list of the top 10 video surveillance software options is available. The resource also includes analysis of various surveillance technologies, with attention to specialized AI functionalities within CCTV. The content is designed for both professional installers and business owners, providing information to assist in making informed decisions about security systems. The site’s guides cover camera selection, current challenges in video surveillance and advanced security protocols.
    [url=https://www.cctv-focus.com/]desktop surveillance software[/url]

  6. Форум предназначен для обсуждения вопросов, связанных с видеонаблюдением и безопасностью. Участники могут делиться информацией и опытом в области программного обеспечения для видеонаблюдения на ПК, совместимого с IP-камерами. Рассматриваются комплексные решения, VMS, CMS, а также технологии AHD и IP. Обсуждаются вопросы настройки, восстановления паролей камер, и применения ИИ-видеоаналитики. Отдельное внимание уделено применению интегрированной видеоаналитики на основе искусственного интеллекта, включая обнаружение объектов, распознавание автомобильных номеров и лиц, а также выявление признаков дыма и огня. Форум является площадкой для обмена знаниями и обсуждения актуальных тем в области видеонаблюдения. Здесь пользователи могут делиться своим опытом, задавать вопросы и находить решения для своих задач. Форум служит платформой для обмена знаниями и поиска решений в сфере видеонаблюдения. [url=https://securityvideo.ru/]видеоаналитика распознавание[/url]

  7. Guatemala has pledged a 40% increase in deportation flights carrying Guatemalans and migrants of other nationalities from the United States, President Bernardo Arevalo announced Wednesday during a press conference with US Secretary of State Marco Rubio.
    [url=https://kra-33-cc.ru]kra50 cc[/url]
    Guatemala has also agreed to create a task force for border control and protection along the country’s eastern borders. The force, composed of members of the National Police and army, will be tasked with fighting “all forms of transnational crime,” Arevalo said.
    [url=https://kra-35.ru]kra42 cc[/url]
    Foreign nationals who arrive in Guatemala through deportation flights will be repatriated to their home countries, Arevalo said, adding that the US and Guatemala would continue to have talks on how the process would work and how the US would cooperate.
    [url=https://kra-40-at.ru]kra44 at[/url]
    Arevalo also said that Rubio has voiced his support for developing infrastructure projects in the Central American nation. He added that his government would send a delegation to Washington in the coming weeks to negotiate deals for economic investments in Guatemala – which he said would incentivize Guatemalans to stay in their home country and not migrate to the US.

    Arevalo said Guatemala has not had any discussions about receiving criminals from the US as El Salvador’s president has offered. He also insisted his country has not reached a “safe third country” agreement with the United States, which would require migrants who pass through Guatemala to apply for asylum there rather than continuing to the US.
    kra39.at
    https://at-kra37.cc

  8. Wells such as Longhorn A — which for most operators would be economically worthless since they lack pipeline capacity to transmit any gas they produce — are a model for giving new life to aging wells. Bitcoin mining offers them a new revenue stream and, as one bitcoin mine operator wrote in a 2023 blog post, going off-grid comes with a suite of “superpowers,” including the ability to relocate quickly and to “operate permissionlessly.” Digital & Trend reports We validate arguments from both sides and provide empirical evidence for the extent and energy sources of Bitcoin mining in the U.S., based on data from 13 publicly listed mining companies that account for one-fourth of the total network hashrate as of the end of 2022. Using the grid average emission factors, we find that the carbon intensity of electricity consumed by the 13 miners included in our analysis of 397 gCO2 kWh is nearly equivalent to the U.S. grid average of 387 gCO2 kWh. Furthermore, we find that the annual emissions of 7.2 MtCO2 caused by the 13 analyzed publicly listed miners in the U.S. alone surpass the carbon emissions of the State of Vermont.
    https://avader.org/page/business-services/https-changehero-io-buy-btc
    Cronos (CRO) is the utility token for the Crypto exchange. CRO can be used to pay fees on the platform and also staked to qualify for various benefits. It saw significant growth last year, partly reflecting Crypto’s aggressive promotional activities. Like most of the crypto industry, CRO has struggled so far in 2022. This feedback is never shared publicly, we’ll use it to show better contributions to everyone. Extensive list of supported cryptocurrencies In 2019 Crypto began to launch more services. This included crypto-to-crypto exchange services with the aiming of being a more broad one-stop shop for crypto users that would target the profitable “crypto trader” demographic. In the same year, the company began offering centralized crypto borrowing and lending services in a similar vein to Celsius and BlockFi. These services were called Crypto Earn and Crypto Credit. In 2019, Crypto cards begin shipping to the United States.

  9. Как зарегистрироваться в личном кабинете [url=https://mostbet-wza1.xyz/]mostbet[/url], как пополнить счет, сделать ставку и выиграть деньги на популярном букмекерском сайте.

  10. Заказать такси на курорт и оптимизировать расходы на жд вокзале Симферополя — задача, которая требует ответственного подхода.

    Прежде всего, стоит заранее проанализировать предложениями различных сервисов, чтобы выбрать наиболее удачный вариант. Многие приложения и сайты предоставляют скидки для новых клиентов или акции в определенное время суток.
    https://tratatur.ru/price/taxi-simferopol-simeiz

    Также важно учитывать возможность коллективных поездок, которые значительно снижают стоимость поездки на одного человека. Не забывайте спрашивать стабильные тарифы, чтобы избежать неприятных сюрпризов.

    И, конечно, бронируйте такси до момента поездки — это не только понизит ваши деньги, но и освободит от лишнего стресса в дороге.

  14. https://mostbet-wdz6.top/ о последних обновлениях и возможных блоках доступа к сайту букмекерской конторы Мостбет через актуальное зеркало для удобного доступа к ставкам и функционалу сайта.

  15. Фонбет промокод: MAX777, используйте его при регистрации аккаунта. Новые пользователи могут активировать бонус и получить бездепозитный фрибет до 15 000 рублей. Воспользоваться бесплатно промокодами «Фонбет» для получения подарочной ставки или другого бонуса можно во время создания аккаунта на официальном сайте букмекерской конторы, а также после активации личного кабинета. Действующие промокоды могут давать зачисление кэшбэка на депозит, бесплатное пари и т.п.

    промокод для фонбет

  16. На сегодняшний день доступ к официальному сайту букмекерской конторы мостбет можно получить через рабочее зеркало, обеспечивая полноценную игровую активность сегодня и прямо сейчас.

  17. https://mostbet-wdt5.top/ – как зайти на официальный сайт Мостбет и начать делать ставки на спорт с высокими коэффициентами и удобным интерфейсом.

  19. Желаете скачать Mostbet и начать делать ставки на спортивные события с удобного мобильного устройства? Узнайте, как это сделать быстро и просто в нашей статье!

  21. Выигрывай реальные деньги в лучших казино! Топ слотов, акции, стратегии для победы! Подписывайся
    Игровые автоматы: фишки, стратегии, бонусы! Заработай с нами! Только честные обзоры.
    https://t.me/s/official_1go_1go/670

  22. На сайте мостбет казино вы найдете актуальное зеркало, которое позволит с легкостью получить доступ к игровым автоматам и другим азартным развлечениям.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

87 + = ninety three