Parsing Organizational Hierarchy or Chart of Accounts in Power BI with Parent-child Functions in DAX

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

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.

Introduction

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.

Sample Dataset

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;

Enter Your Email to download the file (required)

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;

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.

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;

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:

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;

PathContains

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.

Summary

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.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

2 Comments

  • Nice method, but if you have a lot of hierarchy levels it’s a bit cumbersome, don’t you think? In that case, or maybe in any case, wouldn’t it be better to just self-join the table it concerns? One draw-back, at least in import mode, could be that you duplicate data. But then again, the method of this blog (assuming import mode) would also generate additional data, right? I’m curious about your opinion on this!

    • Hi Robin.
      You can import the table again and create the relationship between those two. however, this will only make it easier for you to go One level up. if you want to go more levels up, you need another table again, or you need to use PATH functions similar to what I explained. The method I explained is only adding new columns, not the entire table for sure. How many levels of hierarchy you are talking about? this is just a one off set up, even if you have ten levels, you just set it up once, and then you can use it.
      Cheers
      Reza

Leave a Reply

Your email address will not be published. Required fields are marked *