Total Sum based on a column from other table

[Anonymous]

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🙂

[v-luwang-msft · Accepted 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

[ntaylo06]

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.