Forum Discussion
Multiple Relationship between the same tables are all be able to be activated.
Hi Community,
I am not sure if this is an known issue (I checked this community as well as web-searched but could not find any topic on this) or a designed behavior, but I faced a weird thing. Here is an example. There are 2 tables :
Keys (Relationships) are :
[Table 01].[Key01] -> [Table 02].[Foreign02] (one to many, one direction)
[Table 02].[Key02] -> [Table 01].[Foreign01] (one to many, one direction)
Once you created those relationships in PBI Desktop, then I could activate both relationships :
Then, lets select "4" in the Key01 filter. With the data table showing above, there should only be 1 record picked up. But when doing that, I have the below result.
I assumed this behavior :
But actually, it seems it worked like this :
Of course, when I deleted the "[Table 02].[Key02] -> [Table 01].[Foreign01] (one to many, one direction)" relationship or inactivated it, then it worked as expected :
I was wondering if this is an known-issue and/or designed behavior, but it seems the behavior and the result is something wrong.
Please advise.
Thank you.
Hi dkata
Interesting!
My first observation is that Power BI/Tabular models do not support multiple active relationships (or relationship paths) between tables, whether circular or not.
Quoting this documentation:
"There can only be one active filter propagation path between two model tables."
I would say that the bug in this case is that Power BI Desktop permitted this configuration of relationships to exist without throwing an error.
It is difficult (and probably futile) to comment on what the behaviour "should" be in this situation, since the engine is not designed to handle this specific situation.
Normally the Power BI Desktop interface prevents the creation of any active relationship that would create such a situation.
However, you have creatively found a way around this and created such a configuration of relationships, which in this case happens to circular 🙂
I was able to replicate your model setup and activate both relationships by either:
- In the Power BI Desktop Model view, selecting both relationships > Make this relationship active > Apply changes.
- In Tabular Editor, set both relationships to Active.
Did you use either of these two methods or another method?
However, attempting to activate one of the relationships in either the "Manage relationships" or "Edit relationship" dialog box after the other is already active results in the expected error message:
"An active relationship already exists between Table 02 and Table 01. To make this relationship active, deactivate the existing relationship first."
Was there a particular situation you were trying to model here, or was this an example to demonstrate the issue?
Regards
4 Replies
- OwenAuger
Super User
Hi dkata
Interesting!
My first observation is that Power BI/Tabular models do not support multiple active relationships (or relationship paths) between tables, whether circular or not.
Quoting this documentation:
"There can only be one active filter propagation path between two model tables."
I would say that the bug in this case is that Power BI Desktop permitted this configuration of relationships to exist without throwing an error.
It is difficult (and probably futile) to comment on what the behaviour "should" be in this situation, since the engine is not designed to handle this specific situation.
Normally the Power BI Desktop interface prevents the creation of any active relationship that would create such a situation.
However, you have creatively found a way around this and created such a configuration of relationships, which in this case happens to circular 🙂
I was able to replicate your model setup and activate both relationships by either:
- In the Power BI Desktop Model view, selecting both relationships > Make this relationship active > Apply changes.
- In Tabular Editor, set both relationships to Active.
Did you use either of these two methods or another method?
However, attempting to activate one of the relationships in either the "Manage relationships" or "Edit relationship" dialog box after the other is already active results in the expected error message:
"An active relationship already exists between Table 02 and Table 01. To make this relationship active, deactivate the existing relationship first."
Was there a particular situation you were trying to model here, or was this an example to demonstrate the issue?
Regards
Thank you OwenAuger for your comment.
I knew it that PBI does not allow to have multiple relationships between the same tables activated. But my co-worker found this behavior by chance and was wondering if this is an known-issue / design or bug which has already been reported to MS, that is the reason I posted it in this community.
It seems this is kind of bug which I am not sure when it started (maybe the recent update, NOV2023?), do you think it better to report this behavior as a bug to MS?
Thank you in advance for your comment and support.
- OwenAuger
Gotcha, understood 🙂
I'm not aware that it's been reported, so I suggest we report it!
The ability to do this in Power BI Desktop appears to date back to the introduction of the Relationship Pane, which was in preview from October 2022. I just tested in that version and was able to replicate this model setup 🙂
Thanks again for raising this!
Regards
