This is a classic “Slowly Changing Dimension” problem. There are a lot of different approaches you can use to address SCDs, but this is the approach I would take:
- In Power Query, combine the three tables I’ve marked in red into a single employee table. Each employee may have multiple entries, depending on how many degrees they have.
-
In PQ, merge the hourly rate info into that newly created employee table using Degree as the key
-
In PQ, add an index column starting at 1 to the merged table created above
-
In PQ (or as a DAX created column, though PQ is preferred) create a lookup function to add the index value from the Employee dimension table (3. above) to your fact table. This function will basically match on Employee and pull the index value corresponding to the MAX start date for that employee. @Martin has provided an example of this approach here:
- Join your consolidated Employee dimension table to your fact table using that index column in a 1:M unidirectional relationship, and you should be good to go.
I hope this is helpful.
- Brian