Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I am getting an error by using fields from each tables of my model into the same table visual. I think I understand the error but I want to be sure.
Here's my model :
Here's what I am using into the report (in blue the table visual and it's content, in orange the content of each table) :
I am getting the following error while I expected to get the results below (got by SQL query) :
As I am understanding, Power BI rather than joining every results in a kind of cartesian product prefers to not display anything and showing an error. Am I right on this and is there any way to display the kind of results I am getting with a SQL query ?
Thanks a lot !
Artkus
Solved! Go to Solution.
Hi @Anonymous
Due to your relationship is many to one, this may be a logic problem in power bi.
I build two kinds of relationships one to one and many to one.
Build table visuals:
Many to one get error and one to one is right.
If you want to get all name columns in one visual, you may build a new table by Merge in Power Qury Editor.
Select Company Table and Company Inspector Table
Then we select Inspector Name Column and ID_Inspector in Expand.
Result:
Then we do the same steps to merge this table with other two tables.
Result:
Build a Table visual, result is right.
You can download the pbix file from this link: Relationships error - Cartesian product
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you all for your answers. I will go with the custom that do the joins 🙂
HI @Anonymous ,
Can you try moving "Company ID" column along with other columns in the table visual and see if you still get same error?
Thanks,
Pragati
Hi @Anonymous
Due to your relationship is many to one, this may be a logic problem in power bi.
I build two kinds of relationships one to one and many to one.
Build table visuals:
Many to one get error and one to one is right.
If you want to get all name columns in one visual, you may build a new table by Merge in Power Qury Editor.
Select Company Table and Company Inspector Table
Then we select Inspector Name Column and ID_Inspector in Expand.
Result:
Then we do the same steps to merge this table with other two tables.
Result:
Build a Table visual, result is right.
You can download the pbix file from this link: Relationships error - Cartesian product
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HI @Anonymous ,
Is it possible to share your pbix file?
Also, I found a similar thread for this issue:
https://community.powerbi.com/t5/Desktop/issue-with-relationships-between-3-tables/td-p/439734
You can try modifying your relationships like don't go for many-to-many relationships. Try creating One-to-many relationship from COMAPNY table to other 3 tables. Ceck if the above thread helps on what is going wrong at your end.
You may try creating a new table using CROSSJOIN as mentioned in the above thread.
Thanks,
Pragati
@Anonymous , As your group by coming from Many sides. You can keep only one Table from "Many side" tables. So make all other as max of Min
Take as min/max for either inspector_name or intermediary_name
Hello @amitchandak ,
Thanks for your answer. Unfortunately I am not sure what you mean by "taking max of min". Do you mean take last or first for the name ?
All tables are linked by the Company ID so it should be good.
Hope this help.
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |