Data model, inactive relationship, ambiguity

[Anonymous]

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!

[v-jingzhang]

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.

[Anonymous]

@amitchandak - so what should I do instead?

[amitchandak]

@Anonymous , from users, you can reach table 3 via two paths. That is not  correct.