How to Handle Parent-Child Hierarchies in DAX — DAX in Power BI — Chapter 9

5 DAX functions solve the parent-child hierarchy problem in a data model.


Last two-three months, I am studying DAX deeply and going through different books. During this learning process, I found various interesting functions in DAX. Out of them, the parent-child function is so impressive to me.

In this blog, I thought to share with you my experimental learning process about parent-child DAX functions.

What is Parent-Child Hierarchies

As the name suggested, it is related to the parent-child relationship.

For example, in your dataset, if you have some employees and managers information you have to use these DAX Parent-Child functions to find out the relationships between employee and his/her manager.

There is no straightforward method, but there are some functions that will help to derive these functionalities. 

How many functions we have

According to Microsoft documentation, there are five parent and child functions in DAX.

But how will I use this function and what type of data I need to implement this? 


Create Data Set

When I started my career, people asked me to write a SQL query to showcase employee and manager relationship. 

So, I thought to create a similar type of dataset in Power BI using Enter Data feature.

Enter Data option in Power BI
Image by Author

From the above dataset, you can visualize how employee and manager relationships are defined. I am sure you want to revalidate that. Let’s verify from the below image.

Image by Author

Now we will understand how each parent-child functions are behaving.

PATH

As the name suggested, the Path function will provide hierarchy information between parent and child node and display a text delimited value.

Image by Author

PATHITEM

PATHITEM function helps to extract the value from the specified position. For example, if you specify value 2, then it provides the data at the 2nd level in the hierarchy.

Creates 3 calculated columns based on this function and let’s create one report to visualize the relationship. 

Image by Author
Image by Author

From the above report, you can observe some blank rows for Persons_level2 and Persons_level3 columns. Because there is no next level data, so it is displaying like this. 

That means it can repeat the same value. For example, in the first row, Julie is at persons_level1, then for persons_level2 and persons_level3, you can repeat the same name to create a proper visualization. 

Let’s find out what should be the calculated column formula for this.

Image by Author

Now you recreate the above report and replaced it with these two newly created columns.

Image by Author

PATHLENGTH

This is a very simple function. It defines the length of the path. That means it is related to several parents in the path including self. Let’s observe the function, then it will be clear to you.

Image by Author

PATHCONTAINS

It helps to find out the specified value which is mentioned in the formula. 

For example, if you want to know about one employee whether he/she exists or not in the hierarchy.

Image by Author

PATHITEMREVERSE

This function returns the item from the reverse position. That means, it helps to find the manager name, manager’s manager name of an employee.

Image by Author

Additional Column

To complete this case study, you can create one column Persons_IsLeaf. The purpose of this column is to find out whether that corresponding node is a leaf node or not.

Image by Author

Report — Matrix Visual with Spend Amount column

In a real-time scenario, there could be one reporting requirement to find out the total spend amount at the manager level as well as employee level. You can try the below report using Matrix Visual.

Image by author

Download

Please find the code in the below location

DAX — Chapter 9 https://github.com/arpitag1/Power-BI

Video

Conclusion

In this blog, we learn how to handle Parent-child hierarchies using DAX functions.

If you have any questions related to this project, please feel free to post your comments.

Please visit my website for other technical resources.

Please like, comment and subscribe to my YouTube channel which you have already seen. 🙂 Keep Learning.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: