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

# LOOKUPVALUE DAX with Parent-child hierarchy functions

DAX Parent-child functions help us obtain the organizational hierarchy. You can utilize LOOKUPVALUE with parent-child functions to retrieve the names of employees/managers from specified paths.

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

#### So, let’s start. The sample dataset format is as follows:

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 the dataset above, we have columns for EmpId, Dept, EmpName, and Parent Id, with the Parent Id column containing the Employee’s manager Id.

### Let’s get started-

Suppose you want to see the full names of the second-level managers. To achieve this, follow these steps:

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

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

Path DAX

Step-2: Create another calculated column and write the following DAX code below it.

```Level-2 Emp Name =
LOOKUPVALUE(EmployeeData[EmpName],EmployeeData[EmpId],
PATHITEM(EmployeeData[Path],2 ,1))```

#### DAX Description:

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

This function can be used to retrieve a specific level from a hierarchy returned by a PATH function. Here, 2 represents the position, and 1 is the returned integer datatype.

Lookupvalue with Pathitem

#### Now retrieve the names of third-level managers from the specified path.

```Level-3 Emp Name =
LOOKUPVALUE(EmployeeData[EmpName],EmployeeData[EmpId],
PATHITEM(EmployeeData[Path],3 ,1))```

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 , 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