Power Pivot Principles: Understanding Functions for Parent-Child Hierarchies in DAX – Part 3

Welcome back to the Power Pivot Principles blog.  This week, we are going to look at another function for parent-child hierarchies in DAX.

This week, we are going to look at the function: PATHLENGTH.  This is a simple function which returns the number of parents to the specified item in a given PATH result, including itself.

The PATHLENGTH function uses the following syntax to operate:

where:

·         <path> a text expression resulting from evaluation of a PATH function.

Take the data table from previous blog, for example; consider the following HR table:

The table above specifies the relationships between the names and their managers. A1 is at the highest level, so A1 has no manager. A2 is at the subordinate level to A1, but at a higher level to A3 and A4. To better understand this hierarchy, the relationships between the nodes in a tree structure are created:

In our previous practice, we have created a calculated column of Path which specifies the order of a hierarchy structure in PowerPivot.

If we want to calculate the length of each Path, we can use the PATHLENGTH function to extract the length of the hierarchy with syntax below:

In this function, the first parameter is based on the text string in the form of the results of the PATH function and the results would be:

The column Depth shows the length of the hierarchy. For example, for A1, since it is the first level, so the length at this level is one (1). If you refer to record A2, the length is two (2), since it has two levels as A1|A2 in the scenario. The same logic applies to the other items too.

That’s it for this week!

Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.