Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I have a table with two columns from a dimension table (Company and Project) with a Revenue column coming from the Project Fact Table. Both table are related by Project ID.
I wanted to understand why the totals are displayed this way, As it differs from the aggregation totals in SQL.
Also when the realtionship changes it does the aggregation total at a company level
-----------------------
One to many relationship (BOTH direction)
-----------------------------------------------------------------------------------------------------
One to many relationship (SINGLE direction)
---------------------------------------------------------------------------------
Thanks for your help🙂
Solved! Go to Solution.
Hi @Anonymous ,
A relationship can be set to be unidirectional – its default behavior – or bidirectional. In a unidirectional relationship the filter context is propagated from the one-side to the many-side, but not the other way around. In other words, in your diagram a filter on Fact Project Revenue automatically filters Dim_LF_project, whereas a filter on Dim_LF_project propagates neither to Fact Project Revenue.
And the presence of that bidirectional cross-filter introduces ambiguity in the model. A model is ambiguous when there are multiple paths between tables. In an ambiguous model, the engine has multiple options when transferring a filter from one table to another. Therefore, it either finds a preferred way to transfer the filter, or it raises an error. In this scenario, no error was raised; therefore, either the model is not ambiguous (small spoiler: IT IS AMBIGUOUS), or the engine found a preferred way to transfer the filter.
To learn more details ,refer:
https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/
https://docs.microsoft.com/en-us/power-bi/guidance/relationships-bidirectional-filtering
Wish it is helpful for you!
Best Regards
Lucien
Hi @Anonymous ,
A relationship can be set to be unidirectional – its default behavior – or bidirectional. In a unidirectional relationship the filter context is propagated from the one-side to the many-side, but not the other way around. In other words, in your diagram a filter on Fact Project Revenue automatically filters Dim_LF_project, whereas a filter on Dim_LF_project propagates neither to Fact Project Revenue.
And the presence of that bidirectional cross-filter introduces ambiguity in the model. A model is ambiguous when there are multiple paths between tables. In an ambiguous model, the engine has multiple options when transferring a filter from one table to another. Therefore, it either finds a preferred way to transfer the filter, or it raises an error. In this scenario, no error was raised; therefore, either the model is not ambiguous (small spoiler: IT IS AMBIGUOUS), or the engine found a preferred way to transfer the filter.
To learn more details ,refer:
https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/
https://docs.microsoft.com/en-us/power-bi/guidance/relationships-bidirectional-filtering
Wish it is helpful for you!
Best Regards
Lucien
Any time you see a column of all of the same number (in your case 7750) it usually means that relationships in your data are either missing or not working properly. In your 4th/5th/6th screenshots your fact table is not able to filter your dimension table, so it simply returns the entire columns value on each row instead of breaking values out into the proper aggregation.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
175 | |
147 | |
134 | |
105 | |
82 |