Hierarchical Groups Design - best practices

Hi jaryszek,

1. We materialised a flattened group dimension (DimGroup_Flat) with Level0..LevelN columns to enable fast column based drill-down.
2. We maintain a separate GroupHierarchyBridge to capture multiple hierarchy definitions and arbitrary parent–child pairs.
3. We maintain a Bridge_ResourceGrouping to map resources to groups.
4. 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.
5. 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.
6. 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.