Forum Discussion
Advocate IIResolve - Can't determine relationships between the fields
I have 4 tables with the following relationships and slicers directions :
- A - B : 1 to Many / Bidirectional
- B - C : 1 to Many / Bidirectional
- B - D : 1 to Many / Bidirectional
When I try to to create a table visual with a column from table A and table D, the visual says :
Can't determine relationships between the fields.
How to resolve this?
I recommend to change bidirectional relationships to single direction where possible, especially if the bidirectional filtering is not essential for your analysis.
In the contrary case, you can create an explicit measures that use DAX to define the exact calculation and filtering behavior you need. This allows you to manually control the context propagation and filter application across your tables, bypassing some of the ambiguity that automatic filtering can introduce.
For example, if you want to create a measure that aggregates data from table D based on filters applied to table A, you might use a DAX formula that explicitly navigates the relationships:
Measure = CALCULATE(SUM(TableD[SomeColumn]), USERELATIONSHIP(TableA[KeyColumn], TableB[ForeignKeyColumn]), USERELATIONSHIP(TableB[KeyColumn], TableD[ForeignKeyColumn]))
2 Replies
Uzi2019Community Champion
Hi MagicMind
With the relationship you created, you can select correct values from Table A and Table B or Table C and Table B to the “table” chart, but when you add values from the other table, it returns an error like “it can’t determine the relationship between two or more fields”.For example, for 1 from Table A, it can relate 1 or 2 from Table B, since 1 from Table B can also relate 1 or 2 from Table C, so for 1 from Table A, it can’t determine whether 1 or 2 from Table C should be refer to
Table A Table B Table C
1 1 1,2
1 2 1,2
To get which you want as shown in the picture,
You can create a new table with the formula
Table = CROSSJOIN(TableA,TableB,TableC)
I hope I answered your question!
- AmiraBedh
Super User
I recommend to change bidirectional relationships to single direction where possible, especially if the bidirectional filtering is not essential for your analysis.
In the contrary case, you can create an explicit measures that use DAX to define the exact calculation and filtering behavior you need. This allows you to manually control the context propagation and filter application across your tables, bypassing some of the ambiguity that automatic filtering can introduce.
For example, if you want to create a measure that aggregates data from table D based on filters applied to table A, you might use a DAX formula that explicitly navigates the relationships:
Measure = CALCULATE(SUM(TableD[SomeColumn]), USERELATIONSHIP(TableA[KeyColumn], TableB[ForeignKeyColumn]), USERELATIONSHIP(TableB[KeyColumn], TableD[ForeignKeyColumn]))