Issue with circular relationships

[Frede]

HI!

A bit long so please bear with me.

I have a Power BI data model that looks like below. All relationships are single direction and move in the direction of the arrow from ONE to MANY (end of arraw). I am aware that the model should probably be redesigned but this is what I have to work with for now. There are no warnings about circular references by Power BI.

I have a matrix visual that displays a summary of costs for a specific AgencyName. For this AGENCY[AgencyName] is used and a summary measure in the COST and BOOKINGWITHAGENCY Amount column like TotalCost = SUM(COST[Amount]) and TotalBookingWithAgency = SUM(BOOKINGWITHAGENCY[Amount]). 

AgencyName | TotalCost | TotalBookingWithAgency 

Agent 1                 $100          $120

The contents of COST and BOOKINGWITHAGENCY are identical regarding BookingID and Amount (Yes not ideal). So I expected the result to be the same. I noticed a difference in the result of the different measures which I have pinned down to the contents of the INVENTORY table but that got me thinking. 

How does Power BI determine how to relate COST[Amount] to the AGENCY[AgencyName]?

It could either go via

COST -> EVENT -> INVENTORY -> AGENCY

or via

COST -> EVENT -> BOOKING ->BOOKINGWITHAGENCY -> AGENCY

Again, there are no warning about circular dependencies and if I remove the relationship between INVENTORY and EVENT I get the expected correct result of $120. If I keep the relationship between INVENTORY and EVENT and remove the relationship between EVENT and BOOKING I get TotalCost = $100 again.

Therefore it seems that when both relationships between INVENTORY and EVENT and  BOOKING and EVENT exists, it will take the INVENTORY > EVENT path.

1. How does Power BI determine which path to use and can I determine which path is used 

2. I guess the general recommendation would be not to use circular references like this. Why does Power BI not give a warning in this case?

Thanks

Frede

[v-jiascu-msft]

Hi @Frede,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[v-jiascu-msft]

Hi Frede,

Can you share a sample file, please? Only a small file is needed that can show your model.

1. That depends on your data model. We can't choose which path to use. But how we select the columns determines the behavior of the Power BI. For instance, there are three [AgenceName] in three different tables. The result depends on which one we choose. 

2. Just see from your image, there aren't any loops. Because the [Cross Filter Direction] is also the key part of the relationship of Power BI. Please refer to the snapshot. 

BTW, the [Event] table and the [BookingWithAgency] seem to have similar contents.

Best Regards,
Dale