Using values across tables

[jmwdba]

I think I am having a brain freeze because I have done this before in other Power BI tables and I can’t figure out why it’s not working in this particular case. So since neither of my core tables ‘Headcount’ or ‘Discontinuations’ present a unique value to just create a relationship between the two tables because they contain multiple academic years I created a new in-memory table using the following:

Student Code = DISTINCT(SELECTCOLUMNS('Headcount', "Student ID - AY", Headcount[Student ID - AY], "Student ID", Headcount[Student ID], "Academic Year", Headcount[Academic Year]))

Then created the relationships. But when creating visuals I cannot use elements in the ‘Headcount’ table with elements from the ‘Discontinuations’ table. The common field between both tables is the “Student ID” but since a student appears multiple times in both tables based on various registrations and discontinuation statuses they are not unique so I created a unique value in all tables using the following:

For the ‘Headcount’ Table
Student ID - AY = Headcount[Student ID]& " - " &Headcount[Academic Year]

For the ‘Discontinuations’ Table
Student ID - AY = Discontinuations[Student ID]& " - " &Discontinuations[Academic Year]

What am I missing?

The table that I will use principally is the ‘Discontinuations’ table but the ‘Headcount’ Table includes dimensions that I need to view some of the Discontinuations data by.

[Paul]

Hi, create a single column “bridge” table, with the unique Student ID’s, create relationships between this table and the other tables and then use the bridgetable in the field well in your visuals
Paul

[sam.mckay]

Paul is exactly right here.

Firstly please follow the best practice of placing lookup table up the top and fact down the bottom. Trust me on this, it helps so much in understanding what’s happening in your models.

As Paul mentioned you need to create an actual Student table with a unique column of ID and names etc. then link this up.

This is where you would filter anything student name related from then on.

It’s important to always attempt to have the unique values central in a lookup table and then visualize them flowing down a waterfall to filter the fact table. This is how I visualize nearly all data model whenever I can.