Forum Discussion
How to get costs for Specific Group within Bridge Model Design
- 9 months ago
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
thank you very much.
So how this is different than TREATAS? Why this is better?
Best,
Jacek
This uses the physical relationships in the model, whereas TREATAS sets up a virtual relationship. I would expect this would work faster than TREATAS. SQLBI covers this: https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/
It wouldn't hurt to test, although you may need to use PQ or another method to generate a larger dataset to actually notice a difference.
- jaryszek9 months ago
Super User
Thank you very much and thank you for being involved in this topic.
I think this method is the best to avoid using virtual relationships like TREATAS.
But I think this approach from anilelmastasi is the best.
And also tested by SQLBI 😉 Hi is guru!!
Best,
Jacek- anilelmastasi9 months ago
Super User
Hello jaryszek ,
Thanks for your mention 🙂 Is there anything that I can help for, what is the last status?