Lookup table that changes over time

Used for anything related to the new Power BI Desktop and Service tools
Todd S.
Posts: 2
Joined: Fri Oct 30, 2020 1:28 pm

Lookup table that changes over time

Postby Todd S. » Fri Oct 30, 2020 1:41 pm

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.


Posts: 1144
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Lookup table that changes over time

Postby MattAllington » Fri Oct 30, 2020 4:22 pm

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.

Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Todd S.
Posts: 2
Joined: Fri Oct 30, 2020 1:28 pm

Re: Lookup table that changes over time

Postby Todd S. » Fri Oct 30, 2020 10:10 pm

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.

Posts: 65
Joined: Thu Oct 19, 2017 12:57 pm

Re: Lookup table that changes over time

Postby RamanaV » Sat Oct 31, 2020 10:20 am

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/
Ramana Varanasi
Training and Office Manager
Excelerator BI Pty. Ltd.

Return to “Power BI Desktop/Service”

Who is online

Users browsing this forum: No registered users and 3 guests