Microsoft Certified: Power BI Data Analyst AssociateModel the dataHard
A data modeler is building a Power BI model for an HR department. The model includes an 'Employees' table with 'EmployeeID', 'ManagerID', and 'Department'. The 'ManagerID' column refers to 'EmployeeID' within the same table, creating a parent-child hierarchy. The HR department needs to calculate the total number of employees reporting to each manager, including indirect reports (employees reporting to subordinates of a manager). Which DAX function is specifically designed to work with parent-child hierarchies to achieve this kind of calculation?
- APATHCONTAINS
- BPATHITEM
- CPATH
- DPATHLENGTH
Show answer & explanationAnswer & explanation
Correct answer: C. PATH
The PATH function is designed to return a delimited text string with the identifiers of all parents to the current row, starting from the oldest or top-most parent to the current. This function is foundational for working with parent-child hierarchies to enable calculations like summing values down the hierarchy.
Why the other options are wrong
- A. PATHCONTAINS checks if a specific item exists within a PATH result. While PATHCONTAINS is used in the final measure, PATH is the foundational function to first *create* the hierarchical path needed for subsequent analysis of descendants/ancestors.
- B. PATHITEM returns an item from a PATH result at a specified position.
- D. PATHLENGTH returns the number of items in a PATH result.
DAX PATH Function
A DAX function that returns a delimited text string representing the path from the oldest ancestor to a specified item in a parent-child hierarchy.
- Essential for parent-child hierarchy analysis.
- Creates the textual representation of the hierarchy.
- Used in conjunction with other PATH functions (PATHCONTAINS, PATHITEM) for calculations.
Memory trick: Path to the manager, then count the team.