Hidden relationship?

[Anonymous]

I imported a few entities from Microsoft CRM using the OData Feed. When I tried to create a relationship between two of the tables (systemusers and tasks), it automatically made the relationship inactive. When I tried to make it active, it said that there was already another active relationship between the one of the two tables (tasks) and another table (queues) and I would need to deactivate/delete that relationship first. However, when I look at the tables in the Model area, there's no relationship between those two tables! It's also not in the "Manage Relationships" list. It's preventing me from making an important relationship.

Does anyone have an idea of what's going on? Is there such thing as a hidden relationship?

Here's the picture. The inactive relationship that I'm trying to make active is in yellow.

[v-yingjl]

Hi @Anonymous ,

I'm not certain whether your the scenario like this:

When you try to create active relationship between table and table2, it will show this message:

That could be there has indirectly relationships between these tables so that you should enable one of the relationship or change the Cross filter direction.

Please refer:

1. https://radacad.com/what-is-the-direction-of-relationship-in-power-bi
2. https://docs.microsoft.com/en-us/power-bi/desktop-bidirectional-filtering

If not like the previous scenario, could you please share more details about some pictures or error messages for further discussion?

Best Regards,

Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Stachu]

most likely you have bidirectional relationships involving other tables, that are indirectly creating a relationship between the 2.

I would suggest changing the bidirectional relationships to single direction (bidirectional relationships are generally a bad idea in DAX, see more here https://www.sqlbi.com/tv/understanding-relationships-in-power-bi/)

if you post the screen of all your relationships I could then specify which path is causing the issue exactly

[Anonymous]

@Stachu @v-yingjl Thank you for your responses! I uploaded a picture of my issue. It doesn't look like there's bidirectional relationships.

[Stachu · Accepted solution]

the message actually says it quite clearly. Currently there is a following setup:

1. 'queues' => 'systemusers'
2. 'queues' => 'trs_tasktypes'=>'tasks'

so if you add 'systemusers'=>'tasks' then filter on 'queues' could follow 2 paths to filter 'tasks': either the path 2. or this one:

• 'queues' => 'systemusers'=>'tasks'

which causes the ambiguity. The question is which logic you want to implement. If it difers per case then you can activate the inactive relationship by using USERRLATIONSHIP, e.g. in CALCULATE https://dax.guide/userelationship/

more general info on relationships (around 14:30 Alberto starts talking about ambiguity):

https://www.sqlbi.com/tv/understanding-relationships-in-power-bi/

[KingFedUp]

Such a shame that Power BI does not always list which tables are causing the problem. In this case it listed queues and tasks, but as I write this in 2023, when I see such a message there, is no indication as to which of the dozens of tables are causing the problem. But thank you for describing the issue in terms I can understand. I will, at least, know where to look from not on!