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:
|
Level |
Role |
|
1 |
CEO |
|
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:
=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:
=LOOKUPVALUE(
[StaffName],
[StaffId],
PATHITEM([IdPath],2,INTEGER)
)
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):
=IF (
ISBLANK ( PATHITEM ( [IdPath], 6, INTEGER ) ),
Staff[Team Leader],
LOOKUPVALUE (
[StaffName],
[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:
CurrentDepth:=IF (
ISFILTERED ( Staff[Minion] ),
6,
IF (
ISFILTERED ( Staff[Team Leader] ),
5,
IF (
ISFILTERED ( Staff[Junior Manager] ),
4,
IF (
ISFILTERED ( Staff[Manager] ),
3,
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:
ValueToShow:=IF (
staff[CurrentDepth] > max(Staff[NumLevels]),
BLANK (),
SUM('Transaction'[Quantity] )
)
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!
