Power BI DAX: PATH and PATHLENGTH
Mohamad's interest is in Programming (Mobile, Web, Database and Machine Learning). He is studying at the Center For Artificial Intelligence Technology (CAIT), Universiti Kebangsaan Malaysia (UKM).
LevelToCEO = PATHLENGTH(PATH(EmployeeID, ParentEmployeeID))
This expression determines the total count of items in the path from each person to the top of the hierarchy, including the person themselves.
It is useful for dynamically calculating and visualizing hierarchical levels within any organizational structure.
It leverages the PATH() and PATHLENGTH() functions.
The PATH function in DAX creates a string that represents the hierarchical path from an individual to the top of the hierarchy by following the reporting relationships. This path acts as a breadcrumb trail, showing the chain of command.
The PATHLENGTH function then calculates the number of items in this path, effectively counting the levels from the individual to the top. By using these functions together, you can dynamically determine and visualize the number of hierarchical levels each person is from the top of the organization, providing valuable insights into the structure.
How it works:
• PATH(Table[ID], Table[ParentID]): This function creates a string representing the hierarchical path from an individual to the top by following the ParentID relationships.
• PATHLENGTH(PATH(...)): This function calculates the number of items in the path created by the PATH function.
Example Scenario
Consider a company where each employee has an EmployeeID and a ParentEmployeeID that indicates their direct supervisor.
The CEO's ParentEmployeeID is their own EmployeeID, indicating they are at the top of the hierarchy.
Illustration
A table representing the employees and their hierarchical levels:
| Name | EmployeeID | ParentEmployeeID | LevelToCEO |
| David | 100 | 100 | 0 |
| Simon | 101 | 100 | 1 |
| Wenanta | 102 | 100 | 1 |
| Conrad | 103 | 101 | 2 |
| Priyish | 104 | 103 | 3 |
| Sunil | 105 | 103 | 3 |
| Pavel | 106 | 102 | 2 |
How It Works
- David (CEO):
• PATH(Employee[EmployeeID], Employee[ParentEmployeeID]) returns "100".
• PATHLENGTH("100") returns 1.
• LevelToCEO is 0 (since David is the CEO).
- Simon:
• PATH(Employee[EmployeeID], Employee[ParentEmployeeID]) returns "101|100".
• PATHLENGTH("101|100") returns 2.
• LevelToCEO is 1.
- Wenanta:
• PATH(Employee[EmployeeID], Employee[ParentEmployeeID]) returns "102|100".
• PATHLENGTH("102|100") returns 2.
• LevelToCEO is 1.
- Conrad:
• PATH(Employee[EmployeeID], Employee[ParentEmployeeID]) returns "103|101|100".
• PATHLENGTH("103|101|100") returns 3.
• LevelToCEO is 2.
- Priyish:
• PATH(Employee[EmployeeID], Employee[ParentEmployeeID]) returns "104|103|101|100".
• PATHLENGTH("104|103|101|100") returns 4.
• LevelToCEO is 3.
- Sunil:
• PATH(Employee[EmployeeID], Employee[ParentEmployeeID]) returns "105|103|101|100".
• PATHLENGTH("105|103|101|100") returns 4.
• LevelToCEO is 3.
- Pavel:
• PATH(Employee[EmployeeID], Employee[ParentEmployeeID]) returns "106|102|100".
• PATHLENGTH("106|102|100") returns 3.
• LevelToCEO is 2.
This table and explanation illustrate how the PATH and PATHLENGTH functions work together to determine the hierarchical levels from each employee to the CEO.