Creating a new column that references existing columns from two join tables

[em12963]

Hi, I have two tables that have a many to one single cross filter relationship.

Main table:

Key	Student	School_Decile
1	A	0
2	B	2

New_School table:

Key	New_School_Decile
1	1
2	2
3	3

I thought that if I had these two table joined, then I can create a new column within the Main table that references the Programme type.

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

Key	Student	School_Decile	New column
1	A	0	Moved to higher decile
2	B	2	Moved to same decile

New column =

IF (New_School [New_School_Decile] > Main[School_Decile], "Moved to higher decile",

IF (New_School [New_School_Decile] < Main[School_Decile], "Moved to lower decile",

IF (New_School [New_School_Decile] = Main[School_Decile], "Moved to same decile", “IFERROR”)))

However, Power BI doesn’t allow me to reference the columns in the New_School table when I try to create a new column calculation within the Main table. It only lets me reference columns or measures within the Main table. Why is that?

And how do I get around that? I thought that by creating an active join between the two tables, I can then create a calculated column to reference them. Is it because it is due to the join type being a many to one relationship? Help! Please point me in the right direction.

Many thanks!

[FrankAT · Accepted solution]

Hi @em12963,

I have done it around the other. My new column is inside the main table:

Regards FrankAT

[em12963]

Thanks @FrankAT @Anonymous !

That is really helpful.

[Anonymous]

This works:

New column = 

IF (New_School[New_School_Decile] > RELATED(Main[School_Decile]), "Moved to higher decile",

IF (New_School[New_School_Decile] < RELATED(Main[School_Decile]), "Moved to lower decile",

IF (New_School[New_School_Decile] = RELATED(Main[School_Decile]), "Moved to same decile", "IFERROR")))

However, when there is a Key in the New_School tabel, wich is not in Main table it will show all blank rows and a "Moved to higher decile"