Parent-child functions in DAX are very useful for parsing an organizational hierarchy or something like a chart of accounts. Usually, for hierarchies that the number of levels is not determined, you need to use a different method, and parent-child functions in DAX are a big help for that type of hierarchies. In this post, you will learn what functions are involved in this class of functions, and how to use it to parse an organizational chart. The same method can be used for a chart of accounts. If you want to learn more about Power BI, read Power BI book, from Rookie to Rock Star.
Organizational chart or chart of accounts are specific types of hierarchy. Because it is not usually clear that how many levels of hierarchy you get, the hierarchy structure is stored in two columns across the table; ID, and Parent ID. ID usually points to the existing row as the unique key, and Parent ID usually points to another row in the same table as the ID of manager, or parent, or higher level’s member. Only these two columns together build the hierarchy. Here is an example;
DAX has a set of functions named as Parent-child functions very useful for parsing this type of hierarchy. Let’s see how these functions work.
If you want to walk through the example of this post, create a new Power BI Desktop file, and get data from AdventureWorksDW and select DimEmployee as the only table to get data from. Here is how to access the AdventureWorksDW dataset;
Path Function: Finding the entire path from one member
The first function is named as Path. This function gets two parameters; ID and Parent ID. The Path is a very simple function to use. You need to create a calculated column with this function with the code below;
Path = PATH(DimEmployee[EmployeeKey],DimEmployee[ParentEmployeeKey])
Here is the result;
As you can see the output column has all the hierarchy from the current member. The first record’s data means: the current record’s ID is 1, the manager of that is the record with ID of 18, and manager of that is the record with ID of 23, and then the top-level manager is 112. You can see 112 is the top level manager in all records as well.
Finding the Length of Path; PathLength Function
Your next step is to find out how many levels of management you have in the hierarchy. You can use PathLength Function to find out the count of levels for each row. Create a calculated column with PathLength function. This function gets the result of Path function as the input, so you can use the column created in the previous step as the input of this function.
Path Length = PATHLENGTH(DimEmployee[Path])
Here is the result;
To find out the size of hierarchy, you need to find out the maximum PathLength value. You can create a report visual, and show Maximum of Path Length field there to see what is the maximum number of levels in your dataset.
As you can see, the maximum number of levels in the dataset of the example in this blog post is 5.
PathItem; Finding specific levels of the hierarchy
The next step is to create a column for each level of the hierarchy. Using PathItem, you can find out the item for each level of the hierarchy. PathItem gets three parameters;
- The output of the Path function; which we can use our Path calculated column for it.
- The position of the item. Starting from 1, 1 means the highest level (big boss in organizational hierarchy 😉 )
- The output’s data type. 1 means number, 0 means text. We need a number output (to search the employeekey in the table based on that later on), so we use 1 as the input here.
The code will be a calculated column as below;
Organization Level 1 = PATHITEM( DimEmployee[Path], 1, 1)
Here is the sample output;
As you can see 112 is the ID of the big boss and the first level of management in the Path column.
PathItemReverse; start from the lowest level
If you don’t want to start from the highest level, then you can use the PathItemReverse function. Everything will be similar to using the PathItem function; the only difference is that this time, the position starts with index 1 for the lowest level of the hierarchy.
LookupValue; to find the name of the employee
Having just ID of the manager is not usually enough. You may need to get the name of the employee too. In the DimEmployee table, we do not have a full name field. So first add a full name field as below;
Now you can use the LookupValue function to get the full name of the employee that we found by PathItem function. LookupValue asks for three parameters;
- The output column
- The column to search into for the keyword
- the keyword (keyword in our scenario is coming from the result of the PathItem function)
Here is the code altogether for organization level 1:
Organization Level 1 = LOOKUPVALUE( DimEmployee[FullName], DimEmployee[EmployeeKey], PATHITEM( DimEmployee[Path], 1, 1) )
and the result is as below;
Create one column per hierarchy levels
Finally, you need to create one column per hierarchy level. All you need to do is to copy the code for PathItem and LookupValue and only change the position parameter of that. Here is the final result for five levels;
You can then visualize this data using any visuals, but hierarchy slicer custom visual provides a nice output like below, and it acts like a slicer too;
We talked about all Parent-child functions in this example, except PathContains. PathContains is a function that searches through a path for an ID. One example usage of this function is to apply dynamic Row Level Security for an organizational chart. Here in this post, I wrote a full example of using the PathContains function for RLS.
Parent-child functions in DAX are simple to use but very powerful and functional in Power BI and DAX. Using these functions will give you the ability to parse hierarchies such as organizational chart or chart of accounts in a recursive mode. In this post, you have seen an example of using parent-child functions for parsing an organizational chart.