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
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.
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
Hierarchy = PATH ( OrganizationHierarchy[Name], OrganizationHierarchy[Parent])
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.
PATHLENGTH function
PATHLENGTH( path )
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
HierarchyLength = PATHLENGTH(OrganizationHierarchy[Hierarchy])
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
PATHITEM(<path>, <position>[, <type>])
- 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.
ThirdLevel = PATHITEM(OrganizationHierarchy[Hierarchy], 3, TEXT)
Here, PATHITEM returns the 3rd level of hierarchy for each row. If there is no 3rd level available, it returns a blank.
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
PATHITEMREVERSE(<path>, <position>[, <type>])
- 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
ImmediateManager = PATHITEMREVERSE(OrganizationHierarchy[Hierarchy], 2, TEXT)
PATHCONTAINS function
PATHCONTAINS function returns TRUE if the item you are checking for exists within the specified path.
Syntax
PATHCONTAINS( path, item )
- 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.
EmployeesUnderVP3 = PATHCONTAINS(OrganizationHierarchy[Hierarchy], "VP2")
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.
Hi In this we get to know how many people are above him I want to know how many people are below him to find out tier
eg
a –> b–> c–> d–>e
in this case d has the 4 level but I want 2 level as only 1 person works under him
Hi, this is exactly what I’m looking for. Any luck?
hi, it is a very good tutorial.
How would you create the hierachy when there is more than 150 parents and 150 children and the children are not in a column of the table ?
When you have 23 parents you can be patient and create a new column and just write the child of every parent, but when there is a hundred plus parents you cant really do it manually…
Thanks you in advance !
This is great! how can I modify these so I can use them on a dataset that has monthly data? I basically have a table that has employee/manager relationship for each month of the year so need to evaluate the PATH statements so it’s only looking at the same month that each row belongs to. Thanks!!
Bonjour imaginé que nous avons 2 managers qui manage une personne comme adapté la solution