Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Relationships error - Cartesian product

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

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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. 

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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.

  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Pragati11 , 

       

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

       

      • Pragati11's avatar
        Pragati11
        Super User
        6 years ago

        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's avatar
    Anonymous
    Not applicable
    6 years ago

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