{"id":1622,"date":"2026-06-21T13:28:17","date_gmt":"2026-06-21T11:28:17","guid":{"rendered":"https:\/\/www.flip-design.de\/?p=1622"},"modified":"2026-06-21T13:28:17","modified_gmt":"2026-06-21T11:28:17","slug":"implementing-parent-child-hierarchies-in-power-bi-using-path-dax-udfs-and-tmdl","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=1622","title":{"rendered":"Implementing Parent-Child Hierarchies in Power BI Using PATH(), DAX UDFs, and TMDL"},"content":{"rendered":"\n

In many organizations, organizational structures, cost center hierarchies, or product groups are not stored as traditional hierarchies with fixed levels. Instead, they are modeled as so-called parent-child structures, where each organizational unit only references its immediate parent.<\/p>\n\n\n\n

While this approach provides a high degree of flexibility and simplifies maintenance, it can become challenging when visualizing the data in Power BI. Hierarchy slicers, matrix visuals, and drill-down scenarios typically require separate hierarchy levels. In addition, hierarchies with varying depths often result in unwanted (Blank)<\/code> values within reports and visualizations.<\/p>\n\n\n\n

In this article, I will demonstrate how a parent-child hierarchy can be transformed into a report-friendly structure by using the PATH()<\/code> function, the new DAX User Defined Functions (UDFs), and TMDL. Furthermore, I will show how to eliminate unwanted blank values while preserving the complete hierarchy structure.<\/p>\n\n\n\n

The organizational structure is stored in a simple parent-child table. Each row contains a unique ID and references its parent organizational unit through the ParentID<\/code> column.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

Example<\/strong><\/p>\n\n\n\n

Contoso Corporation\n\u2514\u2500 Finance\n   \u2514\u2500 Accounting\n      \u2514\u2500 Accounts Payable\n<\/code><\/pre>\n\n\n\n
For example, Finance<\/strong> has a ParentID<\/code> of 1<\/code> and therefore belongs to Contoso Corporation<\/strong>. Likewise, Accounting<\/strong> has a ParentID<\/code> of 2<\/code> and therefore belongs to Finance<\/strong>.<\/p>\n\n\n\n
ID<\/th>Name<\/th>ParentID<\/th><\/tr><\/thead>
1<\/td>Contoso Corporation<\/td><\/td><\/tr>
2<\/td>Finance<\/td>1<\/td><\/tr>
3<\/td>Accounting<\/td>2<\/td><\/tr>
4<\/td>Accounts Payable<\/td>3<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
To make the hierarchy usable in Power BI, the complete path from the root node to the current element must be generated.<\/p>\n\n\n\n
Hierarchy Structure<\/strong><\/p>\n\n\n\n
Contoso Corporation > Finance > Accounting > Accounts Payable\n<\/code><\/pre>\n\n\n\n
In DAX, this can be achieved using the PATH()<\/code> function:<\/p>\n\n\n\n
Path =\nPATH ( ORG[ID], ORG[ParentID] )\n<\/code><\/pre>\n\n\n\n
The resulting path contains the IDs of all hierarchy levels:<\/p>\n\n\n\n
ID<\/th>Name<\/th>ParentID<\/th>Path<\/th><\/tr><\/thead>
1<\/td>Contoso Corporation<\/td><\/td>1<\/td><\/tr>
2<\/td>Finance<\/td>1<\/td>1<\/td><\/tr>
3<\/td>Accounting<\/td>2<\/td>1<\/td><\/tr>
4<\/td>Accounts Payable<\/td>3<\/td>1<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
At this stage, the hierarchy is available as a technical path and can be further transformed into report-friendly hierarchy levels.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
After creating the hierarchy path using the PATH()<\/code> function, the organizational structure initially exists only in a technical format. The path contains nothing but the IDs of the individual hierarchy levels.<\/p>\n\n\n\n
Example<\/strong><\/p>\n\n\n\n
1|2|3|4\n<\/code><\/pre>\n\n\n\n
While this representation is ideal for calculations, it is difficult to interpret in reports and visualizations. Business users typically want to see meaningful organizational names rather than technical identifiers.<\/p>\n\n\n\n
To make the hierarchy usable for reporting, the individual IDs within the path must be translated back into their corresponding organizational unit names. This is where the new DAX User Defined Functions (UDFs) come into play. Instead of implementing the same logic repeatedly for every hierarchy level, the logic can be defined once and reused throughout the model.<\/p>\n\n\n\n
As a result, the technical path:<\/p>\n\n\n\n
1|2|3|4\n<\/code><\/pre>\n\n\n\n
can be transformed into a user-friendly hierarchy:<\/p>\n\n\n\n
Level 1 = Contoso Corporation\nLevel 2 = Finance\nLevel 3 = Accounting\nLevel 4 = Accounts Payable\n<\/code><\/pre>\n\n\n\n
Using a UDF provides several benefits:<\/p>\n\n\n\n