Project Botticelli

Status message

How to Calculate with Hierarchies in DAX? Purchase this course

10 December 2013 · 5 comments · 6624 views

Loading the video player ...

If this message stays on, please ensure JavaScript is enabled and that you are using a supported device and browser. Ad blockers may also cause this issue.

For more information please visit our device support page.

Data Analysis Expressions

Marco Russo, BI expert from sqlbi.com, discusses ratio-to-parent measures, which use DAX hierarchies, in this short video. You will see how to create such a calculation and how to use it in a Pivot Table (00:30). DAX does not have an internal mechanism for hierarchy navigation, so you need to write the appropriate DAX code in order to obtain the desired results. This technique is available as an Excel feature of the PivotTable, but it requires creating the same calculation in every PivotTable. By creating the measure in the data model itself the calculation becomes available to all pivot tables.

The measure uses a formula (01:00), which contains a series of nested IF statements, checking the result of ISFILTERED for each table column that corresponds to a level of a hierarchy, starting at the bottom. Once the selected level has been detected, a DAX expression is evaluated in order to return the proper calculation of the ratio-to-parent measure. If you are interested in using hierarchies in DAX, including parent-child ones, and if you would like to learn how to perform other hierarchical calculations, make sure to watch Marco’s Hierarchies in DAX in-depth, 55-minute video tutorial.

Purchase A Full Access Subscription

Subscription Best Value

$480/year

Access all content for 1 year.
  Purchase on Tecflix

  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

Comments

markosomppi · 29 October 2014

Missing video?

Rafal Lukawiecki · 29 October 2014

Marko, the video is working when I tried it, but you may need to click on the “play” arrow showing on the screenshot at the top of this page in case you use an iOS browser, otherwise it should play automatically. If it does not, please email us at info@projectbotticelli.com and we will troubleshoot it for you: we will not give up until it plays perfectly for you, I promise that.

Marco Russo · 29 October 2014

The video is at the beginning of this page - maybe you have an issue with your browser? Did you try another browser?

markosomppi · 29 October 2014

Working perfectly with Chrome...thanks ;)

Rafal Lukawiecki · 29 October 2014

Thanks for letting us know, Marko, but it should be working well with all major browsers, as it has been tested that way, too. I’ll drop you an email to find out more about your set-up, but I am glad you are able to watch the videos using Chrome.