Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I try to create some relationships between tables in Power BI. But I always get an error. To understand that error I have created the following example, but I don t understand, why I get the error.
I have the three tables:
and the two tables:
Now I create in Power BI the relationships between the Table, as shown in the following picture.
Then I create a Table visual and drag the fields Name A, Name B and Name C to the Values field. What I expect to get is the following visualisation.
But what happens instead is, that I get the Error
Can t determine relationships between the fields
Can t display the data because Power BI can t determine the relationship between two or more fields.
And if I try to Export the Data, I get the Error
Data exceeds the limit
Your data is too large. Some data sampling may occur.
But if I change Name C to Count of Name C, it works fine.
Does anyone know why this happens, or what I have to change?
Thank you!
Andre
Solved! Go to Solution.
Hi @Puero1
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)
Best Regards
Maggie
Hi @Puero1
Does "ID A" in Table A and "ID B" in Table B has distinct value and they can match each other? How does Table A <-> B look, could you show example data structure?
Best Regards
Maggie
Hi Maggie,
thanks for your reply. Yes they have distinct values and can match each other.
I made a screenshot with all values from all tables. These few values are enough for me to reproduce the error message.
Best Regards
André
Hi @Puero1
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)
Best Regards
Maggie
Hi Maggie @v-juanli-msft
As a design pattern, is it correct to define custom calculated tables as you have suggested and use them in visuals, whenever default data model cannot produce the results we want?
Hi @v-juanli-msft,
thanks, now I understand the error. The CROSSJOIN works perfect for me.
Best Regards
André
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |