Forum Discussion
Memorable MemberBUG::Relationship cardinality issue
You can start reading here Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn and then follow the links.
Hi Element115 ,
The reason for the difference in cardinality when creating relationships in a different order is due to the way Power BI evaluates the relationships between tables. When you create a relationship between two tables, Power BI evaluates the existing relationships between those tables and any other tables in the data model.
In the scenario you described, when you connect DimDate[Date]>RWS[DATE] before DimTime[Time]>RWS[TIME], Power BI evaluates the relationship between DimDate and RWS and determines that there is already a relationship between DimTime and RWS. Since the existing relationship between DimTime and RWS is a many-to-many relationship, Power BI assumes that the relationship between DimTime and RWS should also be many-to-many.
On the other hand, when you connect DimTime[Time]>RWS[TIME] first and then connect DimDate[Date]>RWS[DATE], Power BI evaluates the relationship between DimTime and RWS and determines that there are no existing relationships between any other tables. Therefore, it assumes that the relationship between DimTime and RWS should be one-to-many, and the same for the relationship between DimDate and RWS. This behavior is not a bug, but rather a result of how Power BI evaluates relationships between tables.
Best Regards
Memorable MemberYou wrote: 'Since the existing relationship between DimTime and RWS is a many-to-many relationship, Power BI assumes that the relationship between DimTime and RWS should also be many-to-many.'
I, the user, create the relationships between the DimDate and DimTime and fact tables. So, before having the 2 lines in the screenshot, there is no relationship. Further, there is no *:* relationship between DimTime and the fact table as you write. It is supposed to be a 1:* relationship, which I manage to force Power BI to accept by starting to draw the relationships from the DimTime table first, and DimDate second.
I still don't understand your explanation as if Power BI mistakenly assigning a *:* cardinality to relationship DimTime>RWS were not an error. How can it not be an error? One time to many possible event times in RWS.
And finally, when starting out, there are no relationships defined in the model. So the first relationships I start creating are those between DimDate, DimTime and the fact table RWS. How could this possibly be confusing Power BI, especially when we are dealing with a classic textbook star schema.