Forum Discussion

PBI5851's avatar
PBI5851
Helper V
5 years ago
Solved

Proper relationship mapping recommendation

Hello,

 I have three tables Table A, Table B, Table C. The relationship is such that A is connected to B on a 1-Many relation. A is connected to C on a 1-Many relation. 

 

A.account_id = B.Account_id  (1-Many)

A.account_id = C.Account_id (1-Many)

 

When displaying data from C and A, i dont have any issues. But when i add a column/count from Table B, i get the "cannot display the data because power bi cannot determine the relationship" . Any recommendation on how to resolve this. This may be Mapping 101, but just cant figure it out. One thought was to create an alias, but is that the only approach. 

Need Help
  • v-xicai's avatar
    v-xicai
    5 years ago

    Hi PBI5851 ,

     

    You may use function such as RELATED(), RELATEDTABLE() and USERELATIONSHIP() in your DAX formula for TableB and TableC.

     

    For example:

     

    Count= CALCULATE(COUNT(TableB[Value]),USERELATIONSHIP(TableB[account_id],TableC[account_id]))

     

    In addition, you may go to Query Editor, use "Merge Queries" for TableB and TableC to combine into a single table.

     

    Or you may try to change the Cross filter direction of relationships above from Single to Both.

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • SteveCampbell's avatar
    SteveCampbell
    Memorable Member
    5 years ago

    This is mapping 101 - but this is a difficult topic and takes a while to understand.

     

    I would first try and think of Fact and Dimension tables. It sounds like B and C are fact tables, so should not interact with each other. Instead, you may want to pull out the columns you are filtering on into new dimension tables.

     

    I would start with this and try to understand the concepts first. It may take a bit of learning, but is vital for future model design and will help to avoid a lot of headaches!

    https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

  • v-xicai's avatar
    v-xicai
    Community Support
    5 years ago

    Hi PBI5851 ,

     

    You may use function such as RELATED(), RELATEDTABLE() and USERELATIONSHIP() in your DAX formula for TableB and TableC.

     

    For example:

     

    Count= CALCULATE(COUNT(TableB[Value]),USERELATIONSHIP(TableB[account_id],TableC[account_id]))

     

    In addition, you may go to Query Editor, use "Merge Queries" for TableB and TableC to combine into a single table.

     

    Or you may try to change the Cross filter direction of relationships above from Single to Both.

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.