Hi Guys,How do you model hierarchical groups?My model looks like this :This setup allows me to slice by any level, such as Org to Department to Team, using a hierarchy bridge.However, I’m concerned that introducing two bridge tables and role-playing dimensions might break a clean star schema structure or affect performance.Have you implemented something similar?Is this the right way to handle recursive or ragged hierarchies in Power BI?Would you keep both bridges or simplify it somehow?Any best practices to keep performance and clarity, especially with filter directions, bridge size, or DAX patterns?Thanks for your insights! Best,Jacek

Hi jaryszek, We materialised a flattened group dimension (DimGroup_Flat) with Level0..LevelN columns to enable fast column based drill-down. We maintain a separate GroupHierarchyBridge to capture multiple hierarchy definitions and arbitrary parent–child pairs. We maintain a Bridge_ResourceGrouping to map resources to groups. All relationships are single direction. Group selections are applied to Fact_Costs using explicit DAX with TREATAS to avoid ambiguous or circular filter paths and runtime path computations. The TotalCost_HierarchyAll measure resolves the hierarchy by reading bridge table values directly and filters facts by mapping resource keys into the fact table via TREATAS. The measure does not rely on activating the ParentGroupSK ->DimGroup_Flat relationship. Instead it derives child-group keys from the bridge and uses the resource bridge to identify resources, then applies TREATAS to map to facts. Inactive relationships may remain in the model, but the measure bypasses them using set-based DAX (VALUES / IN / TREATAS). If you have further queries, please contact the Microsoft Fabric community. Thank you.

Hierarchical Groups Design - best practices | Microsoft Fabric Community

15 Replies

Praful_Potphode
Super User
10 months ago
Hi jaryszek

If possible try creating one single dim group table with all possible combinations present in fact table.create one role type with 2 values i.e. child and parent.
Create Custom Hierachy for DIm Group Levels
try to keep cross filter direction single
Minimize Bridge Table row count.Preaggregate and create depth column to count levels depth.
use PATH,PATHITEM,PATHCONTAINS DAX functions for filtering.

please give kudos or mark it as solution once confirmed.

Thanks and Regards,
Praful
- jaryszek
  Super User
  10 months ago
  Thanks,
  
  here I focused on OLAP hierarchy modeling, not OLTP-style parent–child modeling, what you are proposing. With this kind of a runtime DAX queries I can have issues with ragged hierarchies and slower performance which i do not want to have.
  
  Best,
  Jacek
v-pnaroju-msft
Community Support
9 months ago
Hi jaryszek,

Thankyou for the update.

Based on my understanding of the scenario, I have attached a sample PBIX file that may help resolve the issue.

If you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.
New_hierarchy.pbix58 KB
- jaryszek
  Super User
  9 months ago
  Hello,
  
  I see that you divided relationhips and tables from star schema:
  
  why?
  
  Best,
  Jacek
v-pnaroju-msft
Community Support
9 months ago
Hi jaryszek,

I separated the objects to avoid ambiguous relationships, circular dependencies, and unwanted bidirectional filter behaviour. This keeps the model clear, improves filter control, and prevents performance issues caused by multiple relationship paths.

If you have any further queries, please feel free to contact the Microsoft Fabric Community.

Thank you.
- jaryszek
  Super User
  9 months ago
  Hello,
  
  thanks. Can you please share updated file?
  
  Best,
  Jacek
v-pnaroju-msft
Community Support
9 months ago
Hi jaryszek,

We materialised a flattened group dimension (DimGroup_Flat) with Level0..LevelN columns to enable fast column based drill-down.

We maintain a separate GroupHierarchyBridge to capture multiple hierarchy definitions and arbitrary parent–child pairs.

We maintain a Bridge_ResourceGrouping to map resources to groups.

All relationships are single direction. Group selections are applied to Fact_Costs using explicit DAX with TREATAS to avoid ambiguous or circular filter paths and runtime path computations.

The TotalCost_HierarchyAll measure resolves the hierarchy by reading bridge table values directly and filters facts by mapping resource keys into the fact table via TREATAS.

The measure does not rely on activating the ParentGroupSK ->DimGroup_Flat relationship. Instead it derives child-group keys from the bridge and uses the resource bridge to identify resources, then applies TREATAS to map to facts. Inactive relationships may remain in the model, but the measure bypasses them using set-based DAX (VALUES / IN / TREATAS).

If you have further queries, please contact the Microsoft Fabric community.

Thank you.
- jaryszek
  Super User
  9 months ago
  thank you.
v-pnaroju-msft
Community Support
10 months ago
Thankyou, Praful_Potphode, for your response.

Hi jaryszek,

We appreciate your inquiry on the Microsoft Fabric Community Forum.

Based on my understanding, please find below a production safe approach that might help resolve the issue:

Materialize the hierarchy during ETL by creating a single DimGroup that contains precomputed ancestor keys or names and a depth indicator (for example, Level0_SK, Level1_SK, Level2_SK). This enables fast drill-down and column based filtering, instead of relying on string or path based operations at query time.

Retain bridge tables only for genuine many-to-many mappings (for example, Resource ↔ Group with time ranges). If a bridge table exists solely to discover parent lineage, replace it with the ETL flattened dimension.

If changes to ETL are not feasible, calculate ancestry once at refresh time by creating calculated columns during model refresh (not measures) to store ancestor surrogate keys and depth. Use those columns for visuals or measures to avoid performing per render DAX hierarchy computations.

When you must map dimension values to facts, use targeted DAX patterns for filtering (for example, TREATAS, USERELATIONSHIP) instead of relying on bidirectional or complex relationship chains.

Validate performance with DAX Studio or Performance Analyzer using production like data, and limit bridge table rows to active or current mappings only.

Additionally, please refer to the links provided below:
Understand star schema and the importance for Power BI - Power BI | Microsoft Learn
Model relationships in Power BI Desktop - Power BI | Microsoft Learn
Many-to-many relationship guidance - Power BI | Microsoft Learn

We hope this information helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.
- jaryszek
  Super User
  10 months ago
  Thank you,
  
  So, how would you change my proposed model?
  
  I see that you are trying to convince my to use DAX approach and one DimGroup table.
  How you would put Hierarchical groups there?
  
  I updated my model. But I can not show this hierarchical groups like I had using Kimball Model:
  
  How to do this using my new hierarchy model?
  my example:
  https://drive.google.com/file/d/1hbButNYgmA6FbUR5LzYgSCAOhhtpI2-Y/view?usp=sharing
  
  Best,
  Jacek
v-pnaroju-msft
Community Support
9 months ago
Hi jaryszek,

Thankyou for the followup.

Based on my understanding, the hierarchy can be represented by materialising the level columns (Level0_Name → Level1_Name → Level2_Name) within Dim_Group_Flat and creating a model hierarchy from them. This approach provides equivalent drill down capability, improved performance, and a clean star schema.

Please find the attached sample PBIX file for your reference.

We hope the information provided helps to resolve the issue. If you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.
Hierarchial_best.pbix56 KB
- jaryszek
  Super User
  9 months ago
  Thank you, but it will not solve having multiple hierarchical groups.
  It works only for “group ↔ resource” membership.
  
  And if you have multiple hierarchy groups and they can be a parent of multiple hierarchies gruop childs, How yu can model it?
  
  Best,
  Jacek
jaryszek
Super User
9 months ago
Anyone?
v-pnaroju-msft
Community Support
9 months ago
Hi jaryszek,

Thank you for your patience, and apologies for the delayed response.

Please find the updated PBIX file attached, it may help resolve the issue.

If this proposed solution does not resolve your problem, we kindly request that you contact our Power BI certified solution partner using the link: Power BI Partners | Microsoft Power Platform. These professionals can provide in-depth technical assistance and tailored solutions to meet your specific needs.

You are always welcome to continue engaging with the Microsoft Fabric Community as well. If you need further assistance in the future, please reach out via the Microsoft Fabric Community Forum.

Thank you.
U_New_Hierarchy.pbix55 KB
- jaryszek
  Super User
  9 months ago
  Hi sorry but Micirosft Partners is a paid ,very poor quality service. I do not recommend.
  
  Thank you for your solution.
  Can you please explain how this is working?
  
  You added some inactive relationships?
  
  Best,
  Jacek