Let us go through the problem statement:
Parsing the Organizational hierarchy using the DAX in Power BI.
Challenge in this problem is number of levels are not known (not pre-determined), for this power bi has a solution that is parent-child DAX functions.
Let us go through the data structure below, the table (name: Employee) has two columns with the EmployeeId and ManagerId. Here the ManagerId is the next level to the EmployeeId (it is the unique column).
Example: Observe the data below, 5703 manager has multiple low-level employees (5811, 5555).
Path Function:
The first function that we use here is the Path function it takes the two arguments as below.
Create a new column called Path to the existing tablewithbelow formula.
Path = PATH(Employee[EmployeeId], Employee[ManagerId])
The new column appears as below.
The Path column defines the 5459 is the employee (current record), the manager of this employee is 5854, the manager of the 5854-employee record is 5605 and the manager of the 5605-employee record is 5807. It means 5807 is the head/boss of the organization.
Its time to find the number of levels in the organization. Use the below formula and create the new column called PathLength.
PathLength = PATHLENGTH(Employee[Path])
The column added appears as below with the number of levels in for each employee.
Check the column for max PathLength, it is the size of the hierarchy. In our case it is 4.
Its time to find the item (manager) in each level for each employee.
PathItem Function:
To create the column for each level of hierarchy we use the PathItem DAX function. It takes 3 arguments, first argument is the Path column, second is the level of hierarchy (1-4 in our case) and last argument is 1 or 0 if 1 data type is number and 0 is text.
For Level 1 use the below formula,
Level 1 = PATHITEM(Employee[Path], 1, 1)
It gives the highest level of the organization in our case 5807.
Similarly use the below formulas to find the next levels,
Level 2 = PATHITEM(Employee[Path], 2, 1)
Level 3 = PATHITEM(Employee[Path], 3, 1)
Level 4 = PATHITEM(Employee[Path], 4, 1)
The result is shown below.
Add a matrix visual to canvas and drop all these levels from top to bottom and notice the visual.
Find all levels and play with the visual.
Srikanth Kotapatti
Data Analyst
Addend Analytics