# DAX calculations with hierarchies: Set the order straight.

, 2017-05-10 (first published: )

[2017-Apr-30] If you have ever tried to create calculated measures in SSAS Tabular/ Power BI models where different levels of your model hierarchy had to be considered then you'd understand how DAX language doesn't have a straight way to identify parent & child levels in order to compute necessary metric results. There is a good article http://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/ by Alberto Ferrari where he explains a workaround approach to handle hierarchies in DAX.

I made one step further, based on my recent client project experience, and created a new example with one extra level for the Product dimension using Adventure Works Internet Sales tabular database. Here is the code to calculate Sales Ratio to a Parent level for all levels (Category, Sub Category, Model and Product Name)

`RatioToParent:=IF (    ISFILTERED ( Product[HCategory] ),SUM ( 'Internet Sales'[Sales Amount] )        / CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ), ALL ( Product[HCategory] ) ),    IF (        ISFILTERED ( Product[HSubcategory] ),SUM ( 'Internet Sales'[Sales Amount] )            / CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ), ALL ( Product[HSubcategory] ) ),        IF (            ISFILTERED ( Product[HModel] ),SUM ( 'Internet Sales'[Sales Amount] )                / CALCULATE (SUM ( 'Internet Sales'[Sales Amount] ),ALL ( Product[HModel] )                ),            IF (                ISFILTERED ( Product[HProduct] ),SUM ( 'Internet Sales'[Sales Amount] )                    / CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ), ALL ( Product[HProduct] ) )            )        )    ))`

However, when I looked at the results for this new RatioToParent measure using Excel, I saw some weird values: only the to Category level showed me correct calculations and all other Product hierarchy levels had 100%.

Then I changed the order of levels in my DAX calculated measure from (Category > Sub Category > Model > Product Name) to (Product Name >  Model > Sub Category Category):

`RatioToParent_Ordered:=IF (    ISFILTERED ( Product[HProduct] ),SUM ( 'Internet Sales'[Sales Amount] )        / CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ), ALL ( Product[HProduct] ) ),    IF (        ISFILTERED ( Product[HModel] ),SUM ( 'Internet Sales'[Sales Amount] )            / CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ), ALL ( Product[HModel] ) ),        IF (            ISFILTERED ( Product[HSubcategory] ),SUM ( 'Internet Sales'[Sales Amount] )                / CALCULATE (SUM ( 'Internet Sales'[Sales Amount] ),ALL ( Product[HSubcategory] )                ),            IF (                ISFILTERED ( Product[HCategory] ),SUM ( 'Internet Sales'[Sales Amount] )                    / CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ), ALL ( Product[HCategory] ) )            )        )    ))`

and that made the whole difference and resolved the issue of miscalculated results:

So my learning lesson in this exercise was that order of hierarchical levels in DAX calculations is very important. Otherwise, calculations can only be correctly fulfilled for the top parent level of your tabular model hierarchy (please see the first DAX calculations). Lesson learned, mistakes not to be repeated!

# Book Review: Big Red - Voyage of a Trident Submarine

I've grown up reading Tom Clancy and probably most of you have at least seen Red October, so this book caught my eye when browsing used books for a recent trip. It's a fairly human look at what's involved in sailing on a Trident missile submarine...

2009-03-10

# Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

# Inserting Markup into a String with SQL

In which Phil illustrates an old trick using STUFF to intert a number of substrings from a table into a string, and explains why the technique might speed up your code...

2009-02-18

# Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17