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.