Exploring Hierarchical Directories in Power BI: A Step-by-Step Guide

[Ilgar_Zarbali]

Imagine a classic hierarchy often used in Excel or how people naturally perceive it. For example, consider an "Income Statement Item" or a cash flow registry. Here, we have income and expenses, which are broken down into smaller components. This is the hierarchy as humans see it.

Now, let’s switch to how a computer interprets hierarchy. On the second sheet of our Excel file, you’ll see the hierarchical structure represented in a computer-friendly format. It typically involves three columns: ID, Parent ID, and Income Statement Item.

For instance:

• The item Footwear has an ID of 1111, which belongs to the parent group 111.
• The parent group 111 is labeled Revenue from Core Activities, which itself falls under Operating Revenues (ID: 11).
• Finally, Operating Revenues (ID: 11) belongs to the top-level group Revenues (ID: 1).

Hierarchy

In accounting systems, hierarchical directories are written as rows and cascaded for processing.

Problem: Raw Registry Data

The third sheet of our Excel file contains raw registry data, showing lower-level Income Statement Items along with their amounts. However, this data isn’t easy to interpret. Income and expenses are mixed together, making it hard to understand trends.

Registry

Solution: Decoding the Hierarchical Directory in Power BI

We can resolve this by leveraging Power BI. While the task is simple, it’s an essential use case, as there are many hierarchical directories like catalogs, contractors, and more that need to be deciphered in analytics.

Here’s how to get started:

1. Connect to the Excel File

• Load the Hierarchy and Registry tables. No transformations are needed here.

Extract

2. Establish Relationships

• Navigate to the Modeling Tab in Power BI.
• Link the ID column from the Hierarchy table to the Income Statement Item column in the Registry table.

Modeling

3. Create a Matrix Visualization

• Add a new page to your canvas.
• Use the Matrix Visualization and:

- Drag Income Statement Item (from the Hierarchy table) to Rows.

- Drag Amount (from the Registry table) to Values.

This will display the raw data as it comes from accounting systems. However, it’s still not very user-friendly.

Raw Data

Enhancing the Report with DAX

Let’s transform the data using DAX formulas to better represent the hierarchy.

Step 1: Create a Path Column

We’ll use the PATH function to generate the hierarchy path.

Spoiler

Path = PATH('Hierarchy'[ID], 'Hierarchy'[Parent ID])

This function creates a path string, separating hierarchy levels with a vertical bar (|).

Step 2: Extract Hierarchy Levels

Now, let’s add calculated columns for specific levels using the PATHITEM function.

For Level 1, write:

Spoiler

Level 1 = PATHITEM(PATH('Hierarchy'[ID], 'Hierarchy'[Parent ID]), 1, INTEGER)

This retrieves the first element of the hierarchy (e.g., 1 – Revenues).

For Level 2 and Level 3, modify the position parameter:

Spoiler

Level 2 = PATHITEM(PATH('Hierarchy'[ID], 'Hierarchy'[Parent ID]), 2, INTEGER)
Level 3 = PATHITEM(PATH('Hierarchy'[ID], 'Hierarchy'[Parent ID]), 3, INTEGER)

Step 3: Add Descriptive Names

Use LOOKUPVALUE to map IDs to their descriptions:

Spoiler

Level 1 Description = LOOKUPVALUE('Hierarchy'[Income Statement Item], 'Hierarchy'[ID],
PATHITEM(PATH('Hierarchy'[ID], 'Hierarchy'[Parent ID]), 1, INTEGER))

Repeat this for other levels by changing the path position.

Visualizing the Enhanced Hierarchy

1. Update your Matrix Visualization:

- AddLevel 1 and Level 2 descriptions to Rows.

- Drag Amount toValues.

2. Expand the hierarchy by clicking the “Expand to the lowest level” button.

Analytics

Now, you’ll see your Income Statement in a clean, structured format, categorized both classically and hierarchically. This transformation makes the report far more insightful and actionable.

Conclusion

Hierarchical directories are a common feature in analytics, whether for items, contractors, or other datasets. Understanding how to decode and present them in tools like Power BI is an essential skill. With the combination of PATH, PATHITEM, and LOOKUPVALUE, you can make even the most complex hierarchies comprehensible.

Let us know if you found this guide helpful!