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

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: PATHITEM.  This function returns the item at the specified position from a string resulting from evaluation of a PATH function.  It should be noted that positions are counted from left to right.

The PATHITEM function uses the following syntax to operate:

where:

• <path> is a text string in the form of the results of a PATH function
• <position> is an integer expression with the position of the item to be returned
• <type> (optional) is an enumeration that defines the data type of the result.

Take the data table from last week’s blog for example, consider the HR table shown below:

The table 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 as shown below:

Previously, we created a calculated column for Path, which specified the order of a hierarchical structure in PowerPivot:

If we want to extract different levels from this hierarchy, we can use the function PATHITEM.  For example, if we want to extract the first level from the hierarchy, the syntax would be

In this function, the first parameter is based on the text string in the form of the results of the PATH function.  The second parameter defines the level to be extracted.  Applying the same logic to other levels, the results would be:

As indicated by the result above, at Level 1 column, it shows the first level of the hierarchy and the same functionality applies to level 2, level 3 and so on. The results list all different levels like a tree structure for the hierarchy.

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.