Can't display the data because Power BI can't determine the relationship between two or more fields

[Paulien1989]

Hi everyone,

I'm having the issue that I cannot display the data that I want, because Power BI cannot determine the relationship. I'm think it is probably caused by a many table on one end, and another many table on the other. But I would love to hear your thoughts. I cannot share data (Salesforce) because it is confidential, so I'll have to give a hypothetical case instead:

Table 1: Accounts

• Account ID
• Account Name

Table 2: Contacts

• Contact ID
• Account ID
• Contact Name

Table 3: Account Attributes

• Attribute ID
• Account ID
• Attribute Name (e.g. Large size, small size, has a secure website, is GDPR ok, you name it, anything)

Naturally, Accounts and Contacts are related with 1:many. Then, Accounts is also related to Account Attributes with 1:many. I want to create a table visual containing Account Attribute, Account Name and Contact Name. And then, the visual crashes because Power BI cannot determine relationships.

With this table, I want to e.g. sort Accounts by Attributes and then quickly see what Contacts belong with that account, like perhaps in a matrix.

So two questions:

1. Is it possible to transform my data model in such as a way the data can be displayed together?
2. Is it still possible to filter Contacts and data related to Contacts by an Account Attribute, even when they cannot be displayed together?

[alexei7]

Hi @Paulien1989,

Yes, it looks like you are right, and the reason that the information won't display together is because of the two many-to-many relationships.

For question 1, can you display what you would like to see in the resulting table?

For question 2 - yes this is possible. You will need to go into the "Relationships" view of Power BI desktop and from there double-click on the two joins between the relationships and change the "Cross filter direction" to "Both".

Hope that helps,

Alex