Forum Discussion
Data model, inactive relationship, ambiguity
Hey guys,
I have the below data model. When I try to create a relationship between Table 2 and Table 3, I get the error that it would introduce ambiguity between table dates/users and Table 3.
Is there any way to get around this and create an active relationship? I need to be able to use values from Table 1 and 2 to filter Table 3, so I need this connection.
Many thanks!
3 Replies
- amitchandak
Super User
Anonymous , from users, you can reach table 3 via two paths. That is not correct.
amitchandak - so what should I do instead?
v-jingzhangCommunity Support
Hi Anonymous
In Power BI models, model relationships are paths for filter propagation, and these paths must be deterministic. For this reason, a model cannot have multiple active relationships between two tables. In your model, there is already an active path between dates/users and Table 3, so it doesn't allow you to add one more active path between them.
If you want to use Table 1 and 2 to filter Table 3 at the same time, see if it's possible to remove some active relationships between dates/users and Table 1/2. If not possible, you can consider adding one more dates table and users table to the model. One dates/users tables filter Table 1 and the other pair filter Table 2. Then the relationships will be:
1) dates/users 1 > Table 1 > Table 3
2) dates/users 2 > Table 2 > Table 3
Reference: Active vs inactive relationship guidance - Power BI | Microsoft Docs
Another common practice is to use USERELATIONSHIP() function to activate an inactive relationship in DAX codes. This is often used in measures and calculated columns.
Other reference:
UseRelationship or Role-Playing Dimension; Dealing with Inactive Relationships in Power BI - RADACAD
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
