Error: Can't determine relationship between fields

[Anonymous]

Hi

I'm working with test data (limited load rows).

With this test date, in a table, I'm getting the error: "Can't determine relationship between fields" when I try to view fields from two tables (Table1 and Table3).

When I work with full data or I change the relationship in relationship between Table2 and Table3 is set to 1-to-1, the table is displayed correctly.

Does anybody know why?

Thanks 

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i create three tables to test:

When i use 'product'[OrderDate] to filter 'Salesperson'[Salesperson], it shows the same error with you:

This is because the [OrderDate] doesnot has any relationship with [Salesperson] such as one-to-one or one-to-many.

They can just both filtered by 'Date'[Date].

Best Regards,

Giotto Zhi

[freder1ck]

Two years later, I have a workaround. You can make the table visual work by putting an aggregation like count from one of the columns onto the table visual. And then hide that ugly column by resizing it. Using the same dataset at Giotto Zhi, I blogged about it here: 

• https://www.fpk3.com/blog/cant-determine-relationships-between-fields-bidirectional 

[rainer1]

hi,

it is because your relation as you tested by your self. It's hard to say exactly without seeing the tabel data.

My opinion is not all of your data is linked on the many side to one.

Example:

you have 10 rows in table 1 in one column is a id wich you can realate to the next table 2. In table2 you have also 10 row but only two of the rows match with 4 rows in table 1 now you have a one to many relation where 6 fields not linked to another table.

With three tables it will get more complex if you want to filter from table1 to table3

PS- It is not recommended to cross filter direction to both as it can have performance impact

-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!

[mwegener]

Hi @Anonymous ,

Since there is no direct relationship between Tables 1 and 3, the visual does not know how to combine them.

When table 2 is added to the visual, both tables are filtered and displayed via the common table 2.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.