WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 525 reviews for our classroom and online training
How to create and use hierachies in Analysis Services tabular model
Part three of a three-part series of blogs

Hiearchies allow you to group related columns together, to make for prettier pivot tables and happier users. This blog shows you how to create and use hiearchies in SSAS Tabular, considers whether they're worth it, and also looks at the specific case of how to model parent-child hierarchies.

  1. Creating and using hierarchies in SSAS Tabular
  2. Creating hierarchies in Analysis Services Tabular
  3. Modelling parent-child hierarchies in DAX (this blog)

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 11 February 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Modelling parent-child hierarchies in DAX

This blog will show how to take a single table containing parent-child relationships, and turn it into a pivot table. 

Setting up the case study 

To follow this example, you'll need to import the tblStaff table from the MAM database into your model:

The staff table

Give the table a friendly name Staff.

Also, you'll need to create a calculated column to give the full name of each member of staff:

The staff name column

Concatenate the first name of each staff member with a space, and then the last name.

How the staff table stores data (and the aim of this example) 

Consider Richard Madeley (there are some odd people's names in the table ...):

Richard Madeley

We'll track back and see who this person reports to in the tree.

Richard Madeley has staff id number 7, and reports to the manager with staff id number 4 (Joseph Conrad).  He in turn reports to the manager with staff id number 1 (Gladstone Small), who reports ... to no one. 

Hierarchy for Richard Madeley

An organogram for the MAM top-level managers.

What this blog will do is show how to represent this staff hierarchy in a pivot table. 

Step 1 - using PATH to get the id chain 

The first thing we'll do is derive who reports to whom:

The PATH function

The PATH function conveniently extracts a pipe-delimited list of id numbers.

The function used is:

=PATH( [StaffId], [ManagerId] )

Step 2 - calculating the path depth 

You can use the PATHLENGTH function to find how long each id string is:

Path length function

We'll use this to fine-tune the pivot table towards the end of this blog.

Step 3 - finding each person's managerial roles 

I'm going to arbitrarily create the following roles within the MAM company:

Level Role
2 Director 
3 Manager
4 Junior manager
5 Team leader
6 Minion

You can pick out each role by using a function like this:

=PATHITEM([IdPath], RoleNumber,INTEGER)

where RoleNumber can equal any of the numbers 1 through to 6.  For example, to get someone's director, use this formula: 


This would give the following (only the first 3 roles are shown here):

The roles for each person

Richard Madeley has staff member 1 as his CEO, staff member 4 as his director and is a manager.

Step 4 - returning each person's name 

Now we know the id number at each level of hierarchy, we can use the LOOKUPVALUE function to return the relevant person's name.  For example, for the director this would be:






That is, look up the staff name column from the table where the staff id number is the one returned by the PATHITEM function.  This gives:

Names of managers

I've altered each of the 6 calculated columns so that they look up the name corresponding to the staff id number at the respective managerial level in the staff table.


Step 5 - creating a hierarchy

You can now use the 6 columns to create a hierarchy:

Staff hierarchy

I've created a hierarchy called Organogram (see the previous part of this blog for how to do this), which includes the 6 managerial levels.


You will probably also need to create relationships between the Transaction, Pos and Staff tables:

Staff relationships

I've imported the StaffId column for the Pos table, so I can use this to link to the Staff table, to show who served which person.

Step 6 - using the hierarchy in a pivot table

You can now incorporate your Organogram hierarchy into a pivot table:

Organogram hierarchy

I've also hidden the CEO, Director, etc columns from client view, to make the pivot table field list look as clean as possible.


Here's what this would show:

The staff pivot table

Almost perfect! Just a shame about Alex Seddon and his ilk.


Testing for blanks

To get round the awkward fact that for some sales (such as those made by Alex Seddon above) there are no minions or even team leaders, you could amend each level formula to test for blanks.  For example, here's what the formula would now be at level 6 (the Minion formula):

=IF (


Staff[Team Leader],



[StaffId], PATHITEM ( [IdPath], 6, INTEGER )



That is, if there is nothing at level 6 in the path, show the next level up in the hierarchy instead.  If you change the formula for all 6 levels, this will give data for every single column of the table:

Fully populated table

Every level now has a value, even though some are repeats.

Things have improved, but perhaps not much:

Repeated managers

Alex Seddon now appears to report to himself!


Perfecting the pivot table by checking the hierarchy level

To stop multiple Alex Seddons appearing, you can create two new measures as follows:

Measure What it will show
CurrentDepth For any pivot table cell, the current level of hierarchy (1 to 6).
ValueToShow Blank if we've drilled down too far, otherwise the total sales.

Here's an expression you could use for the CurrentDepth measure:

CurrentDepth:=IF (

ISFILTERED ( Staff[Minion] ),


IF (

ISFILTERED ( Staff[Team Leader] ),


IF (

ISFILTERED ( Staff[Junior Manager] ),


IF (

ISFILTERED ( Staff[Manager] ),


IF ( ISFILTERED ( Staff[Director] ), 2, 1 )





Here's what this measure could look like when you create it:

Current hierachy depth measure

The measure in the staff table (it uses the NumLevels calculated column that we referred to earlier).

Here's what the ValueToShow measure would look like:

Check if too low down hierarchy

If we're trying to show data below the maximum number of levels for this member of staff, show a blank instead.


Here's the formula for this measure:

ValueToShow:=IF (

staff[CurrentDepth] > max(Staff[NumLevels]),


SUM('Transaction'[Quantity] )


Finally, you can choose to display the ValueToShow measure in your pivot table:

Final pivot table fields

Show our squeaky new measure.


Finally, the perfect pivot table!

Values summed correctly

Alex Seddon only appears at the appropriate managerial level.


A final question: was it worth it?  At the very least, you'll certainly have learnt a lot about DAX functions if you've followed me all the way to the end of this blog!

This blog has 0 threads Add post