Parent-Child Hierarchies0%

Parent-Child Hierarchies

Beginner12 min read•Updated: Jul 11, 2026

Parent-Child Hierarchies in DAX: PATH, PATHITEM & Organizational Trees

In organizational management, employee reporting structures, and general ledger chart of accounts, data is frequently organized in Parent-Child Hierarchies:

An employee reports to a Manager.
That Manager reports to a Director.
The Director reports to a Vice President.
The Vice President reports to the CEO.

Unlike fixed-depth product hierarchies (Category $\to$ SubCategory $\to$ Product), parent-child hierarchies have uneven and variable depth (one branch may have 3 levels, while another has 7 levels).

1. The DAX PATH Functions

DAX provides specialized functions to unravel recursive parent-child structures into linear, navigable dimensions:

Function	Purpose	Syntax
`PATH`	Generates a delimited text string tracing the lineage from top root to current node.	`PATH(IDColumn, ParentIDColumn)`
`PATHLENGTH`	Returns the depth (level count) of the current node in the hierarchy tree.	`PATHLENGTH(PathColumn)`
`PATHITEM`	Extracts the specific ancestor ID at a designated level of the path.	`PATHITEM(PathColumn, Position, [Type])`
`PATHCONTAINS`	Checks whether a specific ancestor ID exists anywhere within the path (ideal for security!).	`PATHCONTAINS(PathColumn, ManagerID)`

2. Step-by-Step Parent-Child Flattening

Consider Dim_Employee with EmployeeID and ManagerID:

Output

1EmployeeID | EmployeeName  | ManagerID
2---------------------------------------
31          | Satya (CEO)   | BLANK
42          | Scott (EVP)   | 1
53          | Julia (CVP)   | 2
64          | Aarav (Eng)   | 3

Step 1: Create the Hierarchy Path Column

DAX

1EmployeePath = PATH(Dim_Employee[EmployeeID], Dim_Employee[ManagerID])
2-- Result for Aarav: "1|2|3|4"

Step 2: Extract Leveled Hierarchy Columns

DAX

1Level_1_Executive = 
2LOOKUPVALUE(
3    Dim_Employee[EmployeeName],
4    Dim_Employee[EmployeeID],
5    PATHITEM(Dim_Employee[EmployeePath], 1, INTEGER)
6)
7 
8Level_2_VP = 
9LOOKUPVALUE(
10    Dim_Employee[EmployeeName],
11    Dim_Employee[EmployeeID],
12    PATHITEM(Dim_Employee[EmployeePath], 2, INTEGER)
13)
14 
15Level_3_Director = 
16LOOKUPVALUE(
17    Dim_Employee[EmployeeName],
18    Dim_Employee[EmployeeID],
19    PATHITEM(Dim_Employee[EmployeePath], 3, INTEGER)
20)

Now you can drag Level_1_Executive, Level_2_VP, and Level_3_Director into a standard Matrix visual to render a clean, drillable organizational tree!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What DAX function converts a recursive Parent-Child relationship (e.g., EmployeeID and ManagerID) into a delimited string tracing the full lineage path?

Question 2

What delimiter does the DAX PATH function use internally to separate ancestor IDs?

Question 3

How do you determine the depth level of an employee in an organizational hierarchy tree?

Question 4

Which function checks whether a specific manager exists anywhere in an employee's management chain for organizational Row-Level Security?

Question 5

Why is flattening parent-child hierarchies into discrete columns (Level 1, Level 2, Level 3) necessary for Power BI visuals?

Next Lesson

Row-Level Security (Advanced)

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Advanced Time Intelligence	Row-Level Security (Advanced)

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext