Create a new column that references three joined tables

[em12963]

Hi, I have three tables that have a many to one single cross filter relationship with the Main table and the two secondary tables respectively.

Main table:

Key	Student	Pass
1	A	1
2	B	0

Secondary table 1:

Key	School_Decile
1	1
2	2
3	3

Secondary table 2:

Key	School_Decile
1	2
2	4
3	2

I would like to create a new column within the Main table that references the Pass column (from the Main table), and the New School Decile from both the Secondary table 1 and Secondary table 2 respectively.

My desired output is to have the new column in the Main table:

Key	Student	Pass	New column
1	A	1	Moved to lower decile
2	B	0	Failed

New column =

IF (Main[Pass]=0, "Failed",

IF (Secondary table 1 [School_Decile] > Secondary table 2[School_Decile], "Moved to higher decile",

IF (Secondary table 1 [School_Decile] < Secondary table 2 [School_Decile], "Moved to lower decile",

IF (Secondary table 1 [School_Decile] = Secondary table 2 [School_Decile], "Moved to same decile",

 “IFERROR”))))

How do I create the DAX in a new column within the Main table that references all three of those tables?

Any input would be very much appreciated, many thanks in advance!

[parry2k · Accepted solution]

@em12963 add the following expression for the new column in the main table

New column =
SWITCH (TRUE(),
Main[Pass]=0, "Failed",
RELATED('Secondary table 1'[School_Decile]) > RELATED('Secondary table 2'[School_Decile]), "Moved to higher decile",
RELATED('Secondary table 1'[School_Decile]) < RELATED('Secondary table 2'[School_Decile]), "Moved to lower decile",
RELATED('Secondary table 1'[School_Decile])= RELATED('Secondary table 2'[School_Decile]), "Moved to same decile", “IFERROR”,
"Unknown" --catch all
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡