Microsoft Certified: Power BI Data Analyst AssociateModel the dataHard
A data modeler is building a Power BI model for a global company with a complex organizational structure. The 'Employees' table contains 'EmployeeID', 'EmployeeName', and 'ManagerID'. The 'ManagerID' refers to another 'EmployeeID' within the same table. The modeler needs to create a calculation that identifies the top-level CEO in the hierarchy and then count all direct and indirect reports under a specific manager. Which DAX functions are most suitable for navigating this parent-child hierarchy?
- AMAXX and MINX
- BTREATAS and USERELATIONSHIP
- CPATH, PATHITEM, and PATHLENGTH
- DRELATEDTABLE and LOOKUPVALUE
Show answer & explanationAnswer & explanation
Correct answer: C. PATH, PATHITEM, and PATHLENGTH
PATH, PATHITEM, and PATHLENGTH are specifically designed DAX functions for working with parent-child hierarchies, allowing you to traverse, extract elements, and determine depth within such structures.
Why the other options are wrong
- A. MAXX and MINX are iterator functions for finding maximum/minimum values, not for navigating hierarchical structures.
- B. TREATAS applies filters, and USERELATIONSHIP activates inactive relationships; neither is for hierarchy traversal.
- D. RELATEDTABLE and LOOKUPVALUE are for simple lookups across related tables or single values, not for traversing complex hierarchies.
DAX Parent-Child Hierarchy Functions
DAX provides a set of specific functions (PATH, PATHITEM, PATHLENGTH, PATHCONTAINS) to effectively manage and query data organized in parent-child hierarchies within a single table.
- PATH creates a delimited text string representing the path from the root to a specific item.
- PATHITEM extracts a specific item from a path at a given position.
- PATHLENGTH returns the number of items in a path.
- PATHCONTAINS checks if a specific item exists within a path.
- Ideal for organizational charts, bill of materials, or multi-level categories.
Memory trick: Navigating a hierarchy is like finding your way through a family tree with a map.