Relationships in a Model

Hi duesouth ,

As far as I checked based on what I understood, I can see the relationship between these tables are working fine. There is something else in your case which is not working as expected.

I did the following before testing this

1. Merged the pupil, ks2 and cats table into 1 table.

2. Joined classes with the master table based on external id.

3. Created cards showing average values for Ks2 and cats column.

The Below is the screenshot. I think it is working fine for the filter applied on classes table

1. I have clicked on the first row. The Cards match with the value in the table

2. I am doing filtering of classes in a slicer and I see the results are wrong. This is because when a slicer is applied, it is applied separately on the classes table. In your case, the relation flows from pupil to classes and not the otherway around. Hence any filter that you are applying on the classes table won't impact pupil table.

The Solution I can see in this case is both as cross filter direction.

Alternate Solution to Bidirectional Filters:

But if you are not willing to use it, try having one more separate table for classes and set classes to filter pupil. Use USERELATIONSHIP() to activate this in your measures as required.

The Below screenshot shows the data model post introducing the duplicate table for classes.

Coming to the UI, you need to use the slicer for classes from the dup_classes table. Since you are having the relation to the main classes table, this slicer will also filter the classes table. USERELATIONSHIP() will filter the pupil table. 

To leverage the USERELATIONSHIP(), create new measures for your values. Few are given below

ACATSAS = CALCULATE(AVERAGE(pupil[CAT Mean SAS]), USERELATIONSHIP(dup_classes[External Id], pupil[External ID]))

AGPVSScaled = CALCULATE(AVERAGE(pupil[GPVS Scaled Score]), USERELATIONSHIP(dup_classes[External Id], pupil[External ID]))

The Output will look like shown below

If this helps, Kindly mark this as a Solution. would appreciate a Kudo from you!! 

Regards,