Lookup table that changes over time

[Todd S.]

I am building a data model with a data table of G/L expenses, which has a column with an employee ID. There will be 3 years of data. I have also pieced together historical personnel data to fashion a lookup table to calculate expense by job title, as well as geographical region. While most employees would be stable, at least 10% will move to different job titles or regions in that time. I get that I need to limit my lookup table to one record per employee id (which I cannot change), but I also want to collect the historical data. Any thoughts on the best way to handle this? Create a combined key of date / employee ID? Bring in two copies of the employee lookup table? I found the article linked below, which describes the situation pretty well. Would love an opinion on the best path forward. Dataset is not large (~150K rows) and am only planning on one data table, employee lookup, and date lookup.

https://powerpivotpro.com/2019/06/how-can-i-get-a-lookup-table-from-a-slowly-changing-dimension-scd/

[MattAllington]

Slowly changing dimensions is a big topic. There are many ways. The general approaches I like are
1. Store the dim data in the fact table
2. Create a data table of the SCD and use bidirectional cross filtering and virtual filters.

Regarding 1, this relies on you storing the employee name and region in the fact table. As the dimension changes, you can still reference who was in charge at the time of the transaction.

Regarding 2, you would need something like this
Region, staff, from, to
1, Peter, 1/1/2020,10/10/2020
1, Mary, 11/10/2020,9/9/9999

Connect the SCD table to the dim table and turn on bidirectional filtering (via relationships or CALCULATE). use a virtual filter from the calendar table (eg using TREATAS, or FILTER) to filter the SCD for the correct record. It’s a bit complicated, but definitely can work.

HTH

[Todd S.]

Thanks (again), Matt! For this exercise, I'll probably choose option 1. But if the dataset ends up growing I may try implementing #2.

-Todd S.
Indianapolis

[RamanaV]

If you want to learn more about dealing with slowly changing dimensions, please consider the online training course Dimensional Modeling for the Power BI Pro by Matt Allington, Ken Puls and Miguel Escobar https://www.skillwave.training/courses/ ... er-bi-pro/