DAX parent-child functions to implement hierarchies

DAX parent-child functions are used to achieve organizational hierarchy. Power BI does not support the parent-child regime or self-join. If you have a data model that contains a parent-child structure, then power bi offers the following useful functions. With the help of these functions, it is easy to implement a hierarchical organizational structure. These functions include

• Path
• Pathcontains
• Pathlength
• Pathitem
• Pathitemreverse

For the elaboration of the parent-child hierarchy, I am using the Employee CSV dataset. We need to keep the following columns and the rest of the columns you can delete while transforming data into Power BI, as you can see below.

• Employeekey
• ParentEmployeeKey
• FirstName
• LastName
• Title
• DepartmentName

Now we have imported employees dataset into the Power BI; let’s move to the available path functions.

Path function in DAX

The syntax of the path function in dax is 

PATH(<ID_columnName>, <parent_columnName>)

It takes two parameters. The first parameter is the current item, and the second parameter is the parent that may have an association with the first item. It returns a delimited string that contains the current item and all of its parents.

Let’s learn this function by creating a new calculated column in the employees’ dataset.

Path = PATH(Employees[EmployeeKey],Employees[ParentEmployeeKey])

ID_columnName(EmployeeKey) should be the unique identifier within every available row in the table. The data type of this column can be integer or text. The essential rule about data type is, it must have the same data type as parent_columnName(ParentEmployeeKey)

Parent_columnNmae(ParentEmployeeKey) should also follow the same rule that we have learned about the ID_columnName parameter. The data type of both parameters can be integer or text, but they must have identical data types.

Path function used in the table to produce a link in the form of delimited string from customers to their managers and manager to their higher managers and so on. It is limited to the parent-child level, but it also takes related rows of the parent level into account.

No child can exist without its parent; that’s why the parent key must exist in the table; otherwise, it will throw an error.

If you look at the newly created column path, it has the following value for the first row.

112|23|18|1

All keys in this string are delimited by pipe sign. Boss of key 1 is 18, boss of 18 is 23, boss of 23 is 112 and 112 has no boss. So it means he has a higher authority.

Pathcontains function in DAX

Pathcontain function in DAX is used to check if a particular item exists in the provided path. The syntax of pathcontains function is

PATHCONTAINS(<path>, <item>)

It takes two parameters; the first parameter needs to be a path, and the second parameter will be an item that you need to check it exists within the provided path.

There are two ways to build this function. In the first method, you have to create one calculated column that returns the path, and then you can use that calculated column within the pathcontains function.

In the second method, you can directly use the path function within the pathcontains function.

When you want to check if a particular manager of an employee exists in the data, it is convenient to check it. If the item exists in the path, it will return true; otherwise, it will return false.

If the second parameter, which is an item, is an integer, then it is converted into text data type and then evaluate the function.

Let’s try to create another calculated column named pathcontains to search for item 189.

Pathcontains = PATHCONTAINS(PATH(Employees[EmployeeKey],Employees[ParentEmployeeKey]),189)

In this figure, you can where the path column contains 189, it is showing true, and if 189 is not present in the path string, it is returning false. It is one of the useful functions in the parent-child hierarchy.

If you create a power bi dashboard in direct query mode, then the pathcontains function will not be supported.

Pathlength function in DAX

Pathlength function in dax counts the number of levels in the hierarchy. The syntax of the pathlength function is 

PATHLENGTH(<path>)

it counts the number of levels from the provided item to all of its parents, as you can see below.

Levels = PATHLENGTH(PATH(Employees[EmployeeKey],Employees[ParentEmployeeKey]))

In the above figure, you can newly create a column named Levels. It is returning a count of all levels for each row.

If we want to get all the managers from a particular level, we have to use a pathlength function to reach that level to fetch our required employees.

Pathlength function in dax traverse through all the keys in the delimited string returns by the path function and then count them.

Whenever we want to access particular employees at a certain level, we need to hardcode that level. We cannot make it dynamic. For example, we have a total of five levels, and if we want to see the name and designation of each employee, then we have to fetch each employee’s information separately. We have to make five columns for employee name and five columns for employee designation.

Pathitem function in DAX

Pathitem function returns the item at the specified level. We can get items at any available hierarchy level.

The syntax of the Pathitem function is

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

The first parameter is the path, as you have seen in the prior functions. The second parameter is the position, from where you want to fetch your desire item. The third parameter is an enumeration that defines the data type of the returned result.

For the enumeration, you can specify integer or text. If you specify 1, it means it will be an integer, and if you specify 0, it means it is a text. 

If the enumeration data type does not match the return result’s data type, it will throw an error message.

The count of position in pathitem function starts from left to right. Let’s create one new column by using the following dax expression.

Level 2 employee = PATHITEM(PATH(Employees[EmployeeKey],Employees[ParentEmployeeKey]),2,1)

We have provided second position to fetch managers that are at level 2.

In the figure you can see, we are getting 23 on each row because, in the second position of path column, the available employee key is 23. In this way, you can get employees on any level.

One drawback is, if you are in direct query mode, pathitem function is not supported.

Pathitemreverse function in DAX

Pathitemreverse function has the opposite behavior as compared to the pathitem function. In pathitemreverse function, position counting starts from right to left, whereas position counting starts in pathitem function from left to right.

The syntax of the pathitem function is

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

The first parameter of the pathitemreverse function is the path. The second parameter needs to be the position that you have to specify to fetch the item from the particular hierarchy level. The last parameter is an enumeration that can be integer or text, as we have seen in the pathitem function.

Let’ create another calculated column

Pathitemreverse = PATHITEMREVERSE(PATH(Employees[EmployeeKey],Employees[ParentEmployeeKey]),2,1)

Below you can see the output 

As you can see, the first record is 18 because, for pathitemreverse function, position counting starts from right to left. So it would help if you kept this behavior in your consideration while using these functions.

One limitation of using this function is direct query mode. Direct query mode doesn’t support this function.

Lookupvalue function with parent-child hierarchical function

Lookupvalue function in dax is one of the widely used function. It returns the value for the current row that meat the search criteria. 

The syntax for the lookupvalue function is

LOOKUPVALUE(

    <result_columnName>,

    <search_columnName>,

    <search_value>

    [, <search2_columnName>, <search2_value>]…

    [, <alternateResult>]

)

The value from the column you are interested in will be the first parameter called result_columnName, and it cannot be an expression.

The second parameter in the lookupvalue function is like “Where clause.” It cannot be an expression and will be available in the current table. 

In the third parameter, you have to provide scalar value that will be evaluated against the second parameter. If it gets successful, then the first parameter will return the value of that row.

The fourth parameter is an optional parameter for the alternative result. If the criteria don’t meet, then the alternative result or the blank value will be returned. You can add multiple conditions in the lookupvalue function.

Let’s write the following calculated column to return the full name of the second-level managers.

Level2Name = LOOKUPVALUE(Employees[Full Name],Employees[EmployeeKey],PATHITEM(Employees[Path],2 ,1))

Level2Title = LOOKUPVALUE(Employees[Title],Employees[EmployeeKey],PATHITEM(Employees[Path],2 ,1))

The first expression will return the full name, and the second expression will return the title where employeekey will be equal to the employeekey returned by the pathitem function in the second position.

Conclusion

You have learned different hierarchical functions available in Power BI DAX to handle the parent-child hierarchy. These are potent functions that used to solve the complex organizational structure. 

With the lookupvalue function’s help, we can fetch the required values from different levels when combining it with pathitem function.

Below you can watch youtube video about dax parent-child functions that include path, pathitem, pathlength, pathitemreverse, pathcontains and lookupvalue.

You can learn more about Power BI If statement using measure and calculate

For more useful blogs, please visit Learn DAX