Forum Discussion

Puero1's avatar
Puero1
Regular Visitor
8 years ago
Solved

issue with relationships between 3 tables

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 ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    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

Puero1's avatar
Puero1
Regular Visitor
8 years ago

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é

 

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
8 years ago

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

  • Puero1's avatar
    Puero1
    Regular Visitor
    8 years ago

    Hi v-juanli-msft,

     

    thanks, now I understand the error. The CROSSJOIN works perfect for me. :smileyhappy:

     

    Best Regards

    André

  • SuraMan's avatar
    SuraMan
    Icon for Advocate II rankAdvocate II
    7 years ago

    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?