BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Creating and using hierarchies in SSAS Tabular
- Creating hierarchies in Analysis Services Tabular
- Modelling parent-child hierarchies in DAX (this blog)
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:
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:
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 ...):
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.
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 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:
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:
You can pick out each role by using a function like this:
where RoleNumber can equal any of the numbers 1 through to 6. For example, to get someone's director, use this formula:
=PATHITEM([IdPath], 2, INTEGER)
This would give the following (only the first 3 roles are shown here):
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:
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:
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:
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:
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:
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):
ISBLANK ( PATHITEM ( [IdPath], 6, INTEGER ) ),
[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:
Every level now has a value, even though some are repeats.
Things have improved, but perhaps not much:
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:
ISFILTERED ( Staff[Minion] ),
ISFILTERED ( Staff[Team Leader] ),
ISFILTERED ( Staff[Junior Manager] ),
ISFILTERED ( Staff[Manager] ),
IF ( ISFILTERED ( Staff[Director] ), 2, 1 )
Here's what this measure could look like when you create it:
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:
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:
staff[CurrentDepth] > max(Staff[NumLevels]),
Finally, you can choose to display the ValueToShow measure in your pivot table:
Show our squeaky new measure.
Finally, the perfect pivot table!
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!