Relationships error - Cartesian product

[Anonymous]

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

[Anonymous]

Thank you all for your answers. I will go with the custom that do the joins 🙂 

[Pragati11]

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

[Anonymous]

Hi @Pragati11 , 

I tried (even with ID_Company from each tables) and I am still getting the error. 

[v-rzhou-msft · Accepted 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. 

[Pragati11]

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

[amitchandak]

@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

[Anonymous]

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 ? 

[Anonymous]

All tables are linked by the Company ID so it should be good.

• You should check the format of all your columns "Company ID" as it could be the problem. They should have the same data format.

Hope this help.