Forum Discussion
Super UserHow to get costs for Specific Group within Bridge Model Design
Hello Guys,
Power BI model:
Dim_Group (GroupSK) → Bridge_ResourceGrouping (GroupSK, ResourceSK, ValidFrom, ValidTo) → Dim_Resources (ResourceSK) → Fact_Costs (ResourceSK, Date, CostAmount).
I need selecting a Group to filter Fact_Costs (sum CostAmount for resources in that group).
Currently Group filters Bridge but doesn’t reach Costs because the Resources–Bridge relationship is single direction from Resources to Bridge. I can’t use bidirectional relationships.
What’s the best practice layout to let Group filter Costs without bidirectional?
Is Bridge → Fact limited many-to-many the right approach?
Here is my model:
https://drive.google.com/file/d/1glGRdWXFcyrQ3WalvsjFWyLy2kbavVen/view?usp=sharing
Best,
Jacek
Please give me the best performance practices out there.
In your pbix, Fact_Costs[CostAmount] was of text data type, so I used SELECTEDVALUE as that didn't require any data transformation or doing something like SUMX/VALUE. Really, you can use any function or measure where I used SELECTEDVALUE, which is the direction you are already going in your question. E.g.
SUM if Fact_Costs[CostAmount] is a number format:
Costs_Number by Group = CALCULATE( SUM( Fact_Costs[CostAmount_Number] ), Bridge_ResourceGrouping )Same output but leaving column as text:
Costs_Text by Group = CALCULATE( SUMX( Fact_Costs, VALUE(Fact_Costs[CostAmount_Text]) ), Bridge_ResourceGrouping )Or, here is a calc for resource average, to get away from very simple SUM examples:
Resource Cost Avg = CALCULATE( DIVIDE( SUM(Fact_Costs[CostAmount_Number]), COUNTROWS(Dim_Resources) ), Bridge_ResourceGrouping )Table of above measures with Dim_Group[Group Name]:
added resource count to help validate the avg measure
14 Replies
- Zanqueta
Hello jaryszek ,
I dont knows thats it are you expeting for:
Instead of changing the relationship direction, use DAX measures with TREATAS or virtual relationships. This is the recommended approach for bridge tables.
Total Cost for Selected Group = CALCULATE( SUM('Fact_Costs'[CostAmount]), TREATAS( VALUES('Bridge_ResourceGrouping'[ResourceSK]), 'Fact_Costs'[ResourceSK] ) )an additional comment you CostAmount was categorizes such as text. I switch for decimal.
Why This Is Best Practice
- Keeps relationships single-directional (avoids ambiguity and performance issues).
- Scales well for large datasets.
- Fully respects filter context without breaking the star schema.If this response was helpful in any way, I’d be glad to receive a 👍 — just like the joy of seeing a DAX measure work on the first try without needing another FILTER.Please mark it as the correct solution. It helps other community members find the right path (and saves them from another infinite loop 🌀).
- anilelmastasi
Hello jaryszek ,
There are other options to create model. For example if you use this, it will filter as you want.
You can check other options, and check differences with this article:
If this solved your issue, please mark it as the accepted solution. ✅
- jaryszek
Hi,
this is interesting scenario but how to will relate to business terms?
If Resouce can have multiple Groups how can you show it on report?
Best,
Jacek - jaryszek
Ok after analyzing it, this model will not work.
no possible to have in a bridge table only 1 to many to facts.
This is why SQLBI is using Many to Many there...
If you have only 1 resource per 1 tag - yes it is great. But I can have multiple resource across many tags so it means it must be many to many
Best,
Jacek
- MarkLaf
You can enforce the filter from the many side to the one side with the bridge table, either as a filter argument in calculate or a simple conditional to use as a measure filter.
As in:
Bridge measure filter
BridgeFilter = IF( NOT ISEMPTY( Bridge_ResourceGrouping ), 1 )Add to visual filter and set to is not blank
Bridge filter argument
CostAmount by Group = CALCULATE( SELECTEDVALUE( Fact_Costs[CostAmount] ), Bridge_ResourceGrouping )- jaryszek
This is interesting but seems to be workig only with slicers. I remember your solution from my previous posts.
CostAmount by Group = CALCULATE( SELECTEDVALUE( Fact_Costs[CostAmount] ), Bridge_ResourceGrouping )
How this would work?
Why there is a selected value? From slicer?
It would work only with?:CostAmount by Group = CALCULATE(Sum(Fact_Costs[CostAmount]), Bridge_ResourceGrouping )
Best,
Jacek- MarkLaf
In your pbix, Fact_Costs[CostAmount] was of text data type, so I used SELECTEDVALUE as that didn't require any data transformation or doing something like SUMX/VALUE. Really, you can use any function or measure where I used SELECTEDVALUE, which is the direction you are already going in your question. E.g.
SUM if Fact_Costs[CostAmount] is a number format:
Costs_Number by Group = CALCULATE( SUM( Fact_Costs[CostAmount_Number] ), Bridge_ResourceGrouping )Same output but leaving column as text:
Costs_Text by Group = CALCULATE( SUMX( Fact_Costs, VALUE(Fact_Costs[CostAmount_Text]) ), Bridge_ResourceGrouping )Or, here is a calc for resource average, to get away from very simple SUM examples:
Resource Cost Avg = CALCULATE( DIVIDE( SUM(Fact_Costs[CostAmount_Number]), COUNTROWS(Dim_Resources) ), Bridge_ResourceGrouping )Table of above measures with Dim_Group[Group Name]:
added resource count to help validate the avg measure
- Ashish_Mathur
Hi,
If there are no duplicates in the ResourceSK column of the Bridge_ResourceGrouping table, then try this approach
- In PQ, merge the Bridge_ResourceGrouping table into the Fact_Costs table to fetch the GroupSK column from there
- Create a Many to One relationship from the Fact_Cost table to the Dim_Group table
- jaryszek
Hi,
I am on DirectLake not using power query.
