3 Tables with Relationship

[Linschiiii]

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.

[anmolmalviya05 · Accepted solution]

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 !!

[Linschiiii]

Many thanks!! now it Works!