Proper relationship mapping recommendation

[PBI5851]

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. 

[v-xicai · Accepted solution]

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.

[SteveCampbell]

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