Power BI DAX Tutorial Analyzing Parent-Child Hierarchies and Sales Using DAX

Posted by

September 17, 2026

On February 18, 2026

Introduction

Parent-child hierarchy is common in business data, such as employee reporting structures, account roll-ups, or product category groupings. In Power BI, these hierarchies cannot be handled using standard relationships because the data resides in the same table, creating self-referencing challenges.

In this tutorial, you will learn how to use DAX functions to model and analyze parent-child hierarchy effectively. By the end of this guide, you will be able to build clean, interactive hierarchy reports in Power BI that accurately reflect organizational or data structures.

What Are hierarchies

Hierarchy in Power BI is a system where the elements are ranked over one another. They are used to define the exploration paths. A few examples of hierarchy include:

Year
Month
Day
Category
Subcategory
Product

This makes browsing easier.

For this blog, the data set contains 2 tables:

Employees Table: Employees record for AMZ Consulting Pty Ltd.
Sales: Sales Amount associated with each employee.

Parent-Child Hierarchy:

For a parent-child hierarchy, a 1:M relationship exists between the parent and the children. All the columns in the hierarchy belong to the same table; thus, self-referencing relationships cannot be created. Some characteristics of a parent-child hierarchy are:

They are unbalanced or ragged. This means that each parent can have one, many, or no children.
Each node can have a variable depth.
Data is associated with each node, whether it is a parent node or child.

Final Expected Result:

Although Power BI cannot model parent-child hierarchies using standard relationships, DAX enables us to build fully functional hierarchical reports. The following result demonstrates correct aggregation and drill-down behavior across all hierarchy levels.

You will want to browse ‘Ali Noorani,’ and the total sales will be the sum of Ali Noorani’s sales and the employees reporting to Ali Noorani.

It is also worth noticing that Jack Jones cannot be expanded any further because there are no employees reporting to Jack. However, Maryam Hussaini can be expanded because there are 3 employees reporting to her directly and 1 indirectly. The same concept will be applied at all hierarchical levels.

Sam Furimo is reporting to Nick Treloar who is then reporting to Maryam Hussaini so you can expand 4 levels here as compared to 2 levels in Jack Jones.

In a typical company’s database, the hierarchy has been set up by adding a ‘Report to’ column with each ‘Employee ID.’ You cannot create a relationship because both the ‘Employee ID’ and ‘Reports to’ belong to the same table. In this blog, you will learn how to use DAX to calculate expressions that resemble a self-join.

Analyzing Sales in Hierarchies

Here, you will learn how to hide the node if the browsing of the hierarchy exceeded the depth of the node.

To find the sales for each node:

Right-click on the Employee table in the Fields
Click on New measure.

Measure tools open.

Type the following formula in the formula bar and press Enter.

In the expression above:

If the maximum Employee Node Depth (CC) is less than the browsing depth then BLANK() is returned.

Otherwise, the sum of the SalesAmount is returned.

Click on the “$” in the formatting section.
Type “2” in the text box.

Drag and drop Sales at level (CM) from the Employee table to the Values.

Now let’s do the final trick to get the desired result.

Remove Maximum Node Depth (CM) and Browsing Depth (CM) from the values of the matrix.

Conclusion

Handling parent-child hierarchies in Power BI requires careful use of DAX to simulate self-joins and calculate node-based metrics.

By mastering these techniques, you can create interactive, accurate reports that reflect real-world organizational structures and ensure correct aggregation across all hierarchy levels. These skills are essential for building professional Power BI dashboards and advanced reporting solutions.

FAQs

What is a parent-child hierarchy?

A parent-child hierarchy is a hierarchy where all elements exist in the same table, with each child linked to a parent through a “Reports To” or similar column. Unlike standard hierarchies, parent-child hierarchies are unbalanced and can have variable depth.

Why can’t parent-child hierarchies be created using normal relationships?

Since both the Employee ID and Reports To columns exist in the same table, standard relationships cannot be applied. DAX functions like PATH(), PATHITEM(), and LOOKUPVALUE() are used instead to simulate self-joins.

How does PATH() work in Power BI?

PATH() creates a traversal path from a child’s node to the top-level parent, helping Power BI understand hierarchical relationships within a single table.

Further Learning and Resources

To learn more about Power BI, check out our courses here:

Want to learn how Microsoft 365 Copilot transforms everyday work with AI? Check out our Course:

Microsoft 365 Copilot Training Courses

Want to learn more about Power BI and how to make reports more efficiently?

Power BI training in Australia: We offer comprehensive training sessions across various cities, including :

Power BI Crash Course eBook: Download our detailed guide to take your Power BI expertise to the next level, covering topics from basics to advanced with practical examples and step-by-step instructions. (Limited time offer)

Download the Beginner’s Guide to Power BI

Ready to enhance your Power BI skills? Sign up for our training courses and explore our wide range of resources designed to elevate your data analytics capabilities.