×

Power BI – Parent-child Hierarchies in DAX

Power BI

As part of my project activities, I sometimes have to deal with parent-child hierarchies and need to flatten them in Power BI. A common parent-child scenario is Geography when we have Country > State > City hierarchy. Another example is employee details where we have a hierarchy like CEO > VP > Senior Manager > Manager etc.

Hierarchies are organized by levels and each level has a parent-child relationship. DAX provides five functions to manage data that is presented as a parent-child hierarchy.

  • PATH
  • PATHLENGTH
  • PATHITEM
  • PATHITEMREVERSE
  • PATHCONTAINS

Using these functions we can calculate or manipulate parent-child hierarchy to find the entire lineage of parents a row has or how many levels the lineage has to the top parent or who is the parent n-levels above the current row etc.

Let us use the parent-child hierarchy relationship of an Organization structure as an example to see how these DAX functions work.

CEO > Vice Presidents (VP) > Directors (D) > Senior Managers (SM) > Managers (M) etc

Organization Hierarchy

I have loaded some sample data in Power BI. My table has two columns. First Column ‘Name’ list various positions in an organization and the second column ‘Parent’ lists the supervisor for each of those positions.

Organization Hierarchy

 

PATH function

PATH function returns a delimited text which contains all the parents to the current row, starting with the oldest or topmost until current. In other words, the PATH function is used to return the items that are related to the current row value. In our example, on a table that contains employees, the managers of employees, and the managers of the managers, you can return the path that connects an employee to his or her manager.

Detailed information about the PATH function is covered in this blog post Power BI – PATH function for Parent-child Hierarchies in DAX

Let us come back to our example and add a custom column to display the Hierarchy using PATH function

 

[dax] Hierarchy = PATH ( OrganizationHierarchy[Name], OrganizationHierarchy[Parent]) [/dax]

 

Here we are trying to find the Hierarchy path based on the Name column as child and Parent column as Parent. PATH function returns a delimited text which contains all the parents to the current row.Our new Custom Column ‘Hierarchy’ will calculate the Organisational Hierarchy present between employees.

 

DAX PATH Function

 

PATHLENGTH function

PATHLENGTH function returns the number of parents to the specified item in a given PATH result which includes the item itself.
Syntax

[dax]PATHLENGTH( path )[/dax]

The path is the expression resulting from the evaluation of a PATH function. PATHLENGTH function is not supported for use in DirectQuery mode. You can get more information about limitations in DirectQuery models here.Let us add a Custom Column using PATHLENGTH to find the number of parents for each employee

[dax]HierarchyLength = PATHLENGTH(OrganizationHierarchy[Hierarchy])[/dax]

 

Pathlength

 

PATHITEM function

PATHITEM function returns the item at the specified position from a string resulting from the evaluation of a PATH function. Positions are counted from left to right.

Syntax

[dax]PATHITEM(<path>, <position>[, <type>]) [/dax]

 

  • path is a text string in the form of the results of a PATH function.
  • position is an integer denoting the position of the item to be returned.
  • type (Optional) defines the data type of the result (TEXT (0) or INTEGER (1) )

Interesting facts about PATHITEM

    • PATHITEM can be used to return a specific level from a hierarchy returned by a PATH function. For example, you can find the skip-level managers for all employees.
    • If you specify a number for the position that is less than one (1) or greater than the number of elements in the path, the PATHITEM function returns BLANK
    • If type is not a valid enumeration element an error is returned.
    • PATHITEM function is not supported for use in DirectQuery mode. You can get more information about limitations in DirectQuery models here.

Let us add one more ConditionalColumn which will return the 3rd level in Organization Hierarchy.

 

[dax]ThirdLevel = PATHITEM(OrganizationHierarchy[Hierarchy], 3, TEXT)[/dax]

 

Here, PATHITEM returns the 3rd level of hierarchy for each row. If there is no 3rd level available, it returns a blank.

 

PathItem

 

PATHITEMREVERSE function

PATHREVERSE is similar to PATHITEM function and returns the item at the specified position from a string resulting from the evaluation of a PATH function but the positions are counted backward from right to left. PATHITEMREVERSE function is not supported for use in DirectQuery mode.

Syntax

[dax]PATHITEMREVERSE(<path>, <position>[, <type>])[/dax]

 

  • path is a text string in the form of the results of a PATH function.
  • position is an integer denoting the position of the item to be returned.
  • type (Optional) defines the data type of the result (TEXT (0) or INTEGER (1) )

Let us add one more ConditionalColumn which will return the immediate manager in Organization Hierarchy. We are using PATHITEMREVERSE function and trying to get the 2nd level from the right who will be the immediate manager of that employee

[dax]ImmediateManager = PATHITEMREVERSE(OrganizationHierarchy[Hierarchy], 2, TEXT)[/dax]

 

PATHITEMREVERSE

 

PATHCONTAINS function

PATHCONTAINS function returns TRUE if the item you are checking for exists within the specified path.

Syntax

[dax]PATHCONTAINS( path, item )[/dax]

 

  • path is a text string in the form of the results of a PATH function.
  • item is a text expression to look for in the path result.

Interesting facts about PATHCONTAINS

    • PATHCONTAINS returns True if the item is present in path else returns false
    • If item is an integer number it is converted to text and then the function is evaluated. If the conversion fails then PATHCONTAINS returns an error.
    • PATHICONTAINS function is not supported for use in DirectQuery mode.

Let us add one more Conditional Column which will check if an Employee comes under the Vice President ‘VP2’. In order to achieve this, we can use PATHCONTAINS and check if VP2 is present in the path.

 

[dax]EmployeesUnderVP3 = PATHCONTAINS(OrganizationHierarchy[Hierarchy], "VP2")[/dax]

 

PATHCONTAINS

I hope you are clear on these DAX functions to handle hierarchies. Please let me your questions or comments in the Comments section. You can download the pbix file for the examples used in this blog here.

 

Author: simpleBIinsights

Leave a Reply