Forum Discussion
3 Tables with Relationship
Hi Guys!
I'm kinda new to Power BI and I'm not at the end with my knowledge for a certain problem.
I have 3 tables.
Table A:
Just has 2 columns with 30 entries.
| Number | City |
| 1 | Vienna |
| 2 | Berlin |
| 3 | Madrid |
Table B (Setpoint value):
Has various columns but only 2 necessary. with tousands entries
| City | Random | Random | Random | Fruits |
| Vienna | Apple | |||
| Vienna | Banane | |||
| Madrid | Apple | |||
| Berlin | Banana | |||
Table C (actual value):
Same as Table B.
I have now with Table A a Relationship to Table B and one to Table C with City.
And a inactive Relationship from Table B to Table C with Fruit
When I try to make a Matrix to show the difference between B and C I get from one the right values but from the other Table only the Sum.
Matrix Values are:
row: Table A - City
Column: Table B - Fruit
Values: Number of Fruits - Table B & Number of Fruits - Table C
| City | Apple | Apple | Banana | Banana |
| Table B | Table C | Table B | Table C | |
| Berlin | 5 | 8 | 3 | 15 |
| Vienna | 2 | 8 | 7 | 15 |
| Madrid | 1 | 8 | 9 | 15 |
I hope it's understandable what my problem ist.
Hi Linschiiii, Hope you are doing good!
Your problem arises from the inactive relationship between Table B and Table C. In Power BI, inactive relationships do not automatically filter data unless explicitly activated using DAX functions. Here's how you can address this issue:Activate the Relationship Using DAX: Use the USERELATIONSHIP function to activate the inactive relationship in your measures. You need to create measures for the counts in Table B and Table C separately, activating the relationship for Table C.
Create Measures: Create measures for counting the fruits in both Table B and Table C. Use the USERELATIONSHIP function to activate the relationship for Table C.
Here's an example of how you can create these measures:
Fruits_Count_TableC =
CALCULATE(
COUNTROWS('Table C'),
USERELATIONSHIP('Table B'[Fruit], 'Table C'[Fruit])
)Add these measures to your matrix instead of the direct column counts. This will ensure that the inactive relationship is taken into account for Table C.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
2 Replies
Hi Linschiiii, Hope you are doing good!
Your problem arises from the inactive relationship between Table B and Table C. In Power BI, inactive relationships do not automatically filter data unless explicitly activated using DAX functions. Here's how you can address this issue:Activate the Relationship Using DAX: Use the USERELATIONSHIP function to activate the inactive relationship in your measures. You need to create measures for the counts in Table B and Table C separately, activating the relationship for Table C.
Create Measures: Create measures for counting the fruits in both Table B and Table C. Use the USERELATIONSHIP function to activate the relationship for Table C.
Here's an example of how you can create these measures:
Fruits_Count_TableC =
CALCULATE(
COUNTROWS('Table C'),
USERELATIONSHIP('Table B'[Fruit], 'Table C'[Fruit])
)Add these measures to your matrix instead of the direct column counts. This will ensure that the inactive relationship is taken into account for Table C.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Many thanks!! now it Works!
