Home » DAX » LOOKUPVALUE DAX with Parent-child hierarchy functions

LOOKUPVALUE DAX with Parent-child hierarchy functions

ParentchildDAX

DAX Parent-child functions help us to get the organizational hierarchy. You can use Lookupvalue with parent-child functions to get the name of employee/ manager from specified path.

Refer Parent-child DAX functions- PATH, PATHITEM, PATHCONTAIN, PATHLENGTH

So let’s start, sample dataset format as below:

Table Name – EmployeeData

EmpId Dept EmpName Parent Id
1 Manager Sandeep Raturi
2 Team Lead Antonio Moreno 1
3 Team Lead Aria Cruz 1
4 Team Lead Dinesh Raturi 1
5 Developer Matt 4
6 Developer Jhon 4
7 Developer Jacobs 2
8 Developer David 3
9 IT Support Jack 2

In above dataset we have EmpId, Dept, EmpName & Parent Id columns, and Parent Id column contains Employee manager Id.



Let’s get started-

Suppose you want to see full name of the second level managers, so in order to achieve this follow these steps-

Step-1: Create a calculated column to get the levels of hierarchy path to using PATH DAX.

Path = PATH(EmployeeData[EmpId],EmployeeData[Parent Id])
Path DAX

Path DAX

Step-2: Create another calculated column and write below DAX-

Level-2 Emp Name =

LOOKUPVALUE(EmployeeData[EmpName],EmployeeData[EmpId],

PATHITEM(EmployeeData[Path],2 ,1))

DAX Description:

LOOKUPVALUE Returns the value for the row that meets all criteria specified by search conditions.

PATHITEM This function can be used to return a specific level from a hierarchy returned by a PATH function. Here 2 is position & 1 returned integer datatype.

Lookupvalue with Pathitem

Lookupvalue with Pathitem



Now get the name of 3rd level managers from specified path-

Level-3 Emp Name =

LOOKUPVALUE(EmployeeData[EmpName],EmployeeData[EmpId],

PATHITEM(EmployeeData[Path],3 ,1))
Parent child dax

Parent child dax

Refer Parent-child DAX functions- PATH, PATHITEM, PATHCONTAIN, PATHLENGTH

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our Contact form , we will revert to you asap.

1 thought on “LOOKUPVALUE DAX with Parent-child hierarchy functions”

  1. How would you go about sorting the level 3 column by EmpId since there’s duplicate blanks in the level 3 column? Thanks

Leave a Reply