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.
So let’s start, sample dataset format as below:
Table Name – EmployeeData
|2||Team Lead||Antonio Moreno||1|
|3||Team Lead||Aria Cruz||1|
|4||Team Lead||Dinesh Raturi||1|
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])
Step-2: Create another calculated column and write below DAX-
Level-2 Emp Name = LOOKUPVALUE(EmployeeData[EmpName],EmployeeData[EmpId], PATHITEM(EmployeeData[Path],2 ,1))
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.
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))
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.