Parent-Child Hierarchies in Power BI – DAX

This blog post explains how to handle the parent child hierarchy in Power BI. If you come from SQL background, then you might be familiar with Parent Child hierarchy scenarios. Parent child hierarchy scenario is a very common scenario which we usually get in the projects.

Let us consider the table below data.

The above table contains 3 different columns

1. Employee ID – Primary Key of the table
2. Name – Employee Name
3. Manager ID – This holds the hierarchy and the values are represent the Employee ID in the same table.

Let us visualize the above data in the organizational chart to understand the data better and also the available levels.

There are 4 different levels are there in the data.

1. VP
2. Director
3. Manager
4. Employee

SQL Approach

If we want to handle this in SQL then we need to join the same table to get the result. Kind of self-join.

SELECT child.[employee id],

child.NAME,

parent.NAME ParentName

FROM employees child

LEFT JOIN employees parent

ON child.[manager id] = parent.[employee id]

We can apply the different filter to get the different levels as a column.

Let us try to understand how to handle the parent child hierarchy in DAX – Power BI.

If you are need to Power BI DAX, then you might think that SQL approach in Power BI as well like below.

Get the duplicate of the employee table and join them like below.

This is not a recommended approach as we have dedicated set of functions in DAX to handle the Parent child hierarchies.

Parent Child Hierarchy Functions in DAX

We have below DAX functions to handle the parent child hierarchy.

1. PATH
2. PATHCONTAINS
3. PATHITEM
4. PATHLENGTH
5. PATHITEMREVERSE

Let us go through how to use the above function for the same data.

PATH

Check the syntax here.

PATH function helps to get the complete hierarchy of the each row in the data.

Full Hierarchy (PATH) =

PATH ( Employee[Employee ID], Employee[Manager ID] )

If you notice the above result, each row has the parent IDs

As we have 4 different levels, let us check the maximum levels in our data using PATHLENGTH.

PATHLENGTH

refer the syntax here.

It will go through the PATH result and identify the length of the hierarchy.

Levels (PATHLENGTH) = PATHLENGTH(Employee[Full Hierarchy (PATH)])

Let us create 4 columns to cover each level using PATHITEM

PATHITEM

Refer the syntax here

It helps to find the specific item on the full PATH.

PATHITEM ( Employee[Full Hierarchy (PATH)], 1, INTEGER )

The above expression returns only the ID so to get the complete name, need to use LOOKUP function.

Level 1 (VP) =
VAR _ITEM =
PATHITEM ( Employee[Full Hierarchy (PATH)], 1, INTEGER )
RETURN
LOOKUPVALUE ( Employee[Name], Employee[Employee ID], _ITEM )
 
Level 2  = 
VAR _ITEM =
PATHITEM ( Employee[Full Hierarchy (PATH)], 2, INTEGER )
RETURN
LOOKUPVALUE ( Employee[Name], Employee[Employee ID], _ITEM )
 
Level 3 (Manager) = 
VAR _ITEM =
PATHITEM ( Employee[Full Hierarchy (PATH)], 3, INTEGER )
RETURN
LOOKUPVALUE ( Employee[Name], Employee[Employee ID], _ITEM )
 
Level 4 (Employee) = 
VAR _ITEM =
PATHITEM ( Employee[Full Hierarchy (PATH)], 4, INTEGER )
RETURN
LOOKUPVALUE ( Employee[Name], Employee[Employee ID], _ITEM )

PATHITEMREVERSE is same as PATHITEM but it will check the position from right to left (reverse to PATHITEM order)

We achieved the expected result. We still have 2 more PATH functions which added below.

PATHCONTAINS

Check the syntax here.

If we want to check whether any of the PATH item is available in full hierarchy.

Get the Power BI file from here.

Happy Learning!!