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.
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.
Now we will understand how each parent-child functions are behaving.
As the name suggested, the Path function will provide hierarchy information between parent and child node and display a text delimited value.
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.
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.
Now you recreate the above report and replaced it with these two newly created columns.
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.
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.
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.
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.
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.
Please find the code in the below location
DAX — Chapter 9 https://github.com/arpitag1/Power-BI
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.