Relationships in a Model

[duesouth]

I've been working on a model this year, which has grown.  We're finding some filters don't work as expected and I believe this to be because of issues with relationships.

My main table is the pupil data table.  An example is:

External ID

Pupil Name

Gender

Year

Reg

Ethnicity

First Language

SEN

G&T

EAL

In Care

FSM Ever 6

Pupil Premium Indicator

1

One

M

8

8R7

White - British

German

Yes

Yes

F

F

F

2

Two

F

8

8R9

White - British

English

K

No

F

F

F

3

Three

F

8

8R7

White - British

English

No

F

F

F

From different areas and therefore reports from our school MIS, we have 2 tables for looking at their KS2 levels and CAT Tests.

KS2

External ID	GPVS Scaled Score	Reading Scaled Score	Maths Scaled Score
1	83	82	87
2	108	111	108
3	92	97	90

CATS

External ID	CAT Verbal SAS	CAT Quantitative SAS	CAT Non Verbal SAS	CAT Spatial SAS	CAT Mean SAS
1	108	113	93	70	96
2	119	96	105	99	105
3	108	86	98	105	99

If I create a relationship in Power BI - to the pupil table - both are 1:1 relationships.  Is it better to merge the data so all is within the pupil table?

Next, there's the classes table.  Each pupil has multiple subjects/classes (I've just put 3 below for this example).  I'm finding if I link Pupil Data Table\External ID with Classes\External ID, Power BI creates a One to Many relationship which has a single cross-filter direction.  At this point if I try to filter by class on KS2 or CAT Tests with simple cards for the average of each field in the each table, the cards don't change on each filter.  If I change the cross-filter direction to both - that does work - but from what I've watched on YouTube/read, this may not be the way forward...

External Id	Class	Teacher	Subject
1	8w/En3	A English	English
1	8w/Ma2	A Maths	Mathematics
1	8w/ScB	A Science	Science
2	8xy/En1	B English	English
2	8xy/Ma1	C Maths	Mathematics
2	8xy/ScA	C Science	Science
3	8xy/En1	C English	English
3	8xy/Ma2	C Maths	Mathematics
3	8xy/ScC	C Science	Science

After that, we also have their report grades.  Again, each pupil has multiple grades - one for each subject and looks something like this:

External Id	Year	Result	Result date	Resultset	Aspect Name	Subject
1	Year 8	4	01/12/2023	Year  8 Autumn	SC KS3 Mastery Sc	Science
1	Year 8	7	28/11/2023	Year  8 Autumn	SC KS3 Mastery Ma	Mathematics
1	Year 8	6	16/11/2023	Year  8 Autumn	SC KS3 Mastery En	English
2	Year 8	6	01/12/2023	Year  8 Autumn	SC KS3 Mastery Sc	Science
2	Year 8	9	28/11/2023	Year  8 Autumn	SC KS3 Mastery Ma	Mathematics
2	Year 8	3	16/11/2023	Year  8 Autumn	SC KS3 Mastery En	English
3	Year 8	4	01/12/2023	Year  8 Autumn	SC KS3 Mastery Sc	Science
3	Year 8	2	28/11/2023	Year  8 Autumn	SC KS3 Mastery Ma	Mathematics
3	Year 8	4	16/11/2023	Year  8 Autumn	SC KS3 Mastery En	English

What would be the best practice in terms of relationships - type and cross-filter direction to create an efficient model where we can filter by class against KS2, CAT Test and report levels?  We've got other stuff linking in - but breaking it down I think my issues are from these key tables and I'd really appreciate your knowledge.  I worked on MS Access many years ago - and while I understand basically about relationships, in practice with a Power BI model which has grown, I don't believe it's optimal...

[Thejeswar · Accepted solution]

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,

[duesouth]

Thanks for taking the time to look at this - and the very detailed response.  Much appreciated!

[Rupak_bi]

Hi, Power BI by default creates best optimum relationships. Dont worry about that.tables having unique values will work as dimention tables and with multiple values as fact tables. for cross filtering, you need to twek the modelling part. let me know what issue you are facing. 

[duesouth]

Thanks for the reply.  The main problem with the model is when I want to filter by class on a page.  I've got a screen with basic cards for averages for KS2 and CATS - but have been finding if you click on a class, the cards don't change.  I've got the relationships as below as Power BI has created (the KS4 WAG 2023 2024 table is basically the results table I talked about in my original post).  To make it work, I've put the Pupil Data/Classes with Pupils relationship as both way - but when we've added to the model, it hasn't been giving us expected results - so I think I need to get this basic bit right first...