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 error.

 

I have the three tables:

  • Table A with the Items A1 (ID 1) and A2 (ID 2)
  • Table B with the Items B1 (ID 1) and B2 (ID 2)
  • Table C with the Items C1 (ID 1) and C2 (ID 2)

and the two tables:

  • A <-> B (links table A with table B)
  • B <-> C (links table B with table C)

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

 

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

5 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    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

     

    • 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
        Community 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