Forum Discussion
BUG::Relationship cardinality issue
ISSUE:
In Model View, if connecting DimDate[Date]>RWS[DATE] before DimTime[Time]>RWS[TIME], the 'Edit Relationship' dialog will force the relationship DimTime[Time]>RWS[TIME] to have cardinality 'many-to-many.'
However, if you connect DimTime[Time]>RWS[TIME] first, and second, DimDate[Date]>RWS[DATE], the Time relationship cardinality will be 'one-to-many' and the Date relationship as well.
You can start reading here Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn and then follow the links.
12 Replies
Note the incomplete relationships. Power BI just isn't sure what's on the "foreign" side of this composite relationship, and it makes guesses. You can change these after the fact.
It may be inconsistent but I wouldn't call it a bug.
Please explain what makes you write 'incomplete relationships'? What is incomplete about DimDate[Date]>RWS[DATE] and DimTime[Time]>RWS[TIME]?
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
Don't you find this sounds totally weird to design Power BI to do this? I thought the app would wait until the user tells it what the relationships are before reaching its own conclusions that trigger a different app behavior as a consequence.
When you write '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,' do you mean to say by 'there is already a relationship between DimTime and RWS' that there are other relationships already defined to other tables (the lines that are cut off and that connect to other tables not in the screenshot and not relevant to this discussion )?
If that's not what you meant, then how could there 'already [be] a relationship between DimTime and RWS'?
Also, why does it do this only for the relationship DimTime[Time]>RWS[TIME] but not for DimDate[Date]>RWS[DATE] when you reverse the order of how you declare the relationships as explained in the OP? It seems inconsistent, doesn't it? That's what makes me write it's a bug.
You 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.
Anonymous lbendlin I'd like to come back to this issue briefly. I just performed the same actions in the semantic model created in Fabric from the lakehouse UI (the New semantic model button) and was able to establish all the relationships as expected.
So my question now is: Why is it a problem in PBI Desktop, but not in Service/Fabric??
In other words, if in Service/Fabric the engine does not to guess or there are no incomplete relationship issues, why do these then arise in the Desktop version?
Wouldn't you expect PBI Desktop modelling to behave as in the Service given the exact same data source (lakehouse)?
