Best Approach for Creating Customer Key


#1

So as my models become more complex with additional data tables, I am trying to find the most effective way of creating a customer key in my case would be called a student key.

The data tables that come into my model include:
Admissions Data Table
Registration Data Table
Graduation Data Table
Alumni Data Table
Prospective Student Data Table

The one common value between all of the tables is called the Student ID. Is it better to try and create a calculated table that draws the unique IDs from all of the above tables into one to use as a Student Key or is there a better approach?

The goal is to be able to use a measure such as:

Unique Individual = COUNTROWS(VALUES(“Student Key”[Student ID]))

Then when I am creating a report page related to Admissions Data I could use that key and if I need to use or correlate to information from Registration Table I can via the relationship through the new Student Key Table that would be created, in theory.

I hope this makes sense. I want to be able to establish visuals that show relationships between data points in the above tables.


#2

That is a fine way to do this.

Sometimes though it can be done also in the query editor.

Maybe if you have more things to do or have multiple column you want to align to the customer key it can be easier and simpler to complete it here.

First you would reference the table.

Then delete all the column you don’t need.

Then final select the main column (customer key) and go ‘Remove duplicates’

This would set it up in a similar way, but probably just has a bit more flexibility to do other things.