How to Create Hierarchy in Power BI Using DAX

Power BI makes creating standard hierarchies a simple drag-and-drop affair, but what happens when your data has a more complex parent-child relationship, like an employee-manager structure or multi-level product categories all stored in a single table? In these cases, you need a dynamic way to build your hierarchy, and this is where DAX comes in. This tutorial will walk you through a powerful, step-by-step method for creating flexible hierarchies in Power BI using DAX functions.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Why Manually Create a Hierarchy with DAX?

The standard way to create hierarchies in Power BI is by stacking related columns in the Fields pane - for example, dragging State, then City, then Postal Code under a Year > Quarter > Month structure. This works great for columns that represent clearly distinct levels from different tables or within the same table.

However, many businesses deal with self-referencing tables, often called parent-child hierarchies. Common examples include:

• Organizational Charts: A table of employees where each person has an Employee ID and a Manager ID. The Manager ID is just another Employee ID from the same table.
• Product Categories: A single table listing product categories, where "Laptops" might be a sub-category of "Electronics," and "Gaming Laptops" is a sub-category of "Laptops."
• Chart of Accounts: A finance table where ledger accounts roll up into parent accounts.

For these scenarios, the standard drag-and-drop method doesn't work. You can't just stack the "Employee Name" column on top of the "Manager Name" column and expect Power BI to map out the entire organizational structure. You need a way to tell Power BI how to trace the path from the top-level parent (like a CEO) all the way down to an entry-level employee. DAX provides the perfect tools for the job.

Understanding the Key DAX Functions

Before we dive into the steps, let's get familiar with the two superstar DAX functions that make this all possible: PATH() and PATHITEM().

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

The PATH() Function

The PATH() function is the engine of our solution. It traverses a parent-child relationship table and returns a delimited text string that shows the full "path" of identifiers from the top-most parent to the current row's ID.

Its syntax is straightforward:

PATH(<,ID_columnName>, <parent_columnName>)

• <,ID_columnName>: The column containing the unique identifier for each item (e.g., Employee ID).
• <,parent_columnName>: The column that points to the parent's identifier (e.g., Manager ID).

If you have an employee with ID 5 whose manager has ID 2, and that manager's manager is the CEO with ID 1, the PATH() function for employee 5 would return the string "1|2|5".

The PATHITEM() Function

While PATH() gives us the whole family tree branch in one string, PATHITEM() lets us pick out a specific person from that branch. It extracts an item from a specific position within a path string created by PATH().

The syntax looks like this:

PATHITEM(<,path>, <,position>, [, <,type>])

• <,path>: The path string generated by the PATH() function.
• <,position>: The level of the hierarchy you want to extract, counting from the left. Position 1 would be the top-level parent.
• <,type> (Optional): Specifies the data type of the result, typically Text (0) or Integer (1).

Using our "1|2|5" example, PATHITEM("1|2|5", 2, 1) would return "2" — the ID of the person at the second level of that path.

Step-by-Step Guide to Building Your DAX Hierarchy

Let's build an organizational hierarchy using a simplified "Employees" table. This hands-on process solidifies the concepts and gives you a template for your own data.

Step 1: Prepare Your Data Table

First, you need a table with at least three columns: a unique ID, a name or label, and a parent ID. Our sample Employees table in Power BI looks like this:

Employees Table:

Notice that our CEO, Alice, has a BLANK Manager ID. This is critical — the top level of a hierarchy must have no parent for the PATH() function to identify it as the starting point, or "root."

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 2: Create the 'Path' Column

The first DAX expression we'll write creates the path strings. This will become the backbone of our hierarchy.

1. Navigate to the Data view in Power BI and select your Employees table.
2. In the Table tools ribbon, click on New column.
3. Enter the following DAX formula:

Employee Path = PATH(Employees[EmployeeID], Employees[ManagerID])

After you hit Enter, your table will update with the new column, which will look something like this. You can now clearly see the reporting chain for each employee as a delimited string.

Step 3: Extract Each Hierarchy Level

Now we use PATHITEM() to break down the Employee Path string into separate columns for each level of the management chain. We'll create a new calculated column for each level.

1. Create another New column and use a formula to get the highest management level (CEO level):

Level 1 (ID) = PATHITEM(Employees[Employee Path], 1, 1)

Note: The final '1' at the end of the formula specifies that the output should be an integer. It's good practice to align the data types.

1. Repeat this process for each subsequent level you want to visualize. For our example, we'll go down four levels deep.

Create a column for Level 2:

Level 2 (ID) = PATHITEM(Employees[Employee Path], 2, 1)

Create a column for Level 3:

Level 3 (ID) = PATHITEM(Employees[Employee Path], 3, 1)

Your table now has individual columns representing each step in the management chain, populated with employee IDs.

This is functionally correct, but showing IDs in a report isn't very user-friendly. Let’s clean it up.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 4: Make It Readable with Names

Our final step is to translate the IDs in our Level X (ID) columns back into actual employee names. The LOOKUPVALUE() function is perfect for this. It searches for a value in one column and returns a value from another column in the same row.

Create a new column for the name for Level 1:

Level 1 (Name) = LOOKUPVALUE(
    Employees[EmployeeName], 
    Employees[EmployeeID], 
    Employees[Level 1 (ID)]
)

This formula tells Power BI: "Look up the EmployeeName from the Employees table where the EmployeeID matches the value in my Level 1 (ID) column."

Now, repeat this pattern for levels 2 and 3:

Level 2 (Name) = LOOKUPVALUE(
    Employees[EmployeeName], 
    Employees[EmployeeID], 
    Employees[Level 2 (ID)]
)

Level 3 (Name) = LOOKUPVALUE(
    Employees[EmployeeName], 
    Employees[EmployeeID], 
    Employees[Level 3 (ID)]
)

Your table is now complete! You have clear, human-readable columns for each level of your organizational hierarchy.

Visualizing Your New Hierarchy

With the DAX work done, using your new hierarchy in visuals is easy.

1. Go to the Report view.
2. In the Fields pane, right-click on the Level 1 (Name) column and select New hierarchy.
3. This will create a hierarchy named "Level 1 (Name) Hierarchy." Right-click it to rename it something intuitive like "Org Chart."
4. Drag and drop Level 2 (Name) and Level 3 (Name) into your new Org Chart hierarchy, placing them in the correct order.
5. Now you can drag this entire "Org Chart" hierarchy into a visual, like a Matrix table. Add a value to measure (e.g., a count of employees or total sales) and you'll immediately see the drill-down functionality in action. You can click the "+" icons to expand levels and explore your data within the context of your freshly built parent-child structure.

Final Thoughts

While Power BI's standard drag-and-drop capabilities are useful, learning to build hierarchies with DAX unlocks a much deeper level of analysis. By using functions like PATH and PATHITEM, you can transform complex parent-child data — like organizational charts or product catalogs — into structured, explorable hierarchies that truly reflect your business logic.

Building formulas in DAX is a powerful skill, but it also highlights the time and expertise often required to get data ready for analysis. At Graphed, we’re focused on removing that complexity. Instead of writing formulas and configuring hierarchies manually, you can connect your data and just ask questions in plain English - like "Show me total sales by manager, broken down by their direct reports." We instantly build interactive dashboards that answer your questions, letting you go from raw data to actionable insight in seconds, not hours.