Can't create a direct active relationship

[Anonymous]

Hello,

I'm trying to connect a calendar to a database that already has an active Many to Many relationship with another catalog, but when I try to do so, an error of ambiguity appears. The most anoying part is that, those same relationships, are active in another database. Bellow I give an example of this problem:

This is the error message that pops up.

So, for example, the blue arrows are active and working fine, and the red ones are not working. They are the same kind of databases and connections, tough.

I have already checked for format bewtween connections (don't know if it could cause problems) and are the same. Am I making any mistake?

Thanks in advance!

[V-pazhen-msft]

@Anonymous 

Can you show us the screens of the model relationship, so we can see how did you connect the many and one size tables. You can take a look at this blog of ambiguity relationship: 

http://cloudbi.com.au/how-to-manage-ambiguous-relationship-in-power-bi/#:~:text=Power%20BI%20and%20SSAS%20models%20cannot%20handle%20a,Tag%3A%20ambiguous%20relationship%2C%20Circular%20paths%2C%20SSAS%20modeling%2054321

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Anonymous , Make sure you are not creating a relationship loop or two paths. Like A- B and B- C also Have A-C this means two path. Also if both A-B and B-C are M to M bi-direction then it will create a problem.

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

[edhans]

Are you sure you need a Many to Many? There are absolutely reasons for them, but I would use them as a last resort. I try to use bridge tables first. M2M can cause ambiguity issues in the model you do not expect. See below an article from MS on M2M relationships and alternatives to a direct M2M setting.

Microsoft recommends minimizing use of both Many-to-Many and Bi-Directional Relationships. In other words, unless you are a DAX expert, find another way to remodel your data to conform to a Star Schema and don't use these two features. I avoid them both at all costs.
Microsoft Guidance on Many-To-Many Relationships
Microsoft Guidance on Bi-Directional Relationships
Microsoft Guidance on Importance of Star Schema

Outside of that, you can still create it as inactive, and activate it on a per-measure basis using:

CALCULATE(
    expression,
    USERELATIONSHIP(Table1[Column1],Table2[Column1])
    )

[Anonymous]

The problem is that Many to Many is the only possible relationship, that power bi allows whe I make the connection.

[edhans]

Not if you create a bridge table between them @Anonymous 

This is a modeling problem, and M2M can be tricky. I am not saying that M2M is the wrong choice here, but doing M2M is really an advanced Power BI modeling concept, as you've accidentally found out.

And that said, I gave a code snippet of how you can keep it as inactive, but activate it in specific measures.

I cannot tell you how many models I've seen that had bi-directional or M2M relationships that returned bad/wrong results because the creator didn't understand the full implications. Removing those and changing the model fixes many of those issues.