Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Bassehave
Helper I
Helper I

Changing values in a dimention table year by year

Hello gurus,

I have run into an issue regarding our data and I'm not quite sure how to fix it in the best / most efficient way, so hopefully you will be able to offer your support.

 

The issue is that we have a dimention table that we use / have used for years, but now some of these values have changed and now you might say, then change the values in the table, but this is where the problem is. If I change the values in the data, all of my old data will have the new values and not the value which it had "back in the day".

 

Zip CodeStore
1C
2B
3A
4A
5B
6A

 

 

This is a basic lookup table, where Zip Code is unique and it returns the store that belongs to this Zip code. 

When looking at the table in 2023, it now looks like this: (A new store has opened and some of the Zip codes, now belong to the new store "D")

Zip CodeStore
1C
2B
3D
4D
5B
6A

 

But I don't want to loose the data integrity of what it looked like back in the day.

So now on to the solution, I was thinking of just adding a "Year" column and when linking the tables, then link by both Zip Code and Year. For this solution I see two ways of doing it:

Zip CodeStoreYear
1A2022
2B2022
3C2022
1D2023
2B2023
3C2023

 

Problem here is, now the Zip Code column is no longer unique. The other option is:

Zip Code20222023
1CC
2BB
3AD
4AD
5BB
6AA

 

Now we are back to the Zip Code, being the unique table, but the problem here is that I can't merge tables based on a changing column header.


Do you know of a solution for this problem?

I have the Year and Zip Code column in both tables already.

 

Thanks in advance,

B

1 ACCEPTED SOLUTION
some_bih
Super User
Super User

Hi @Bassehave check link below as possible two solutions for your case, part

Same store sales with snapshot and 

model view  for 

FIGURE 6 The data model requires a weak MMR relationship between Date and StoreStatus.

 https://www.daxpatterns.com/like-for-like-comparison/ 

 

Did I answer your question? Kudos appreciated / accept solution





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






View solution in original post

1 REPLY 1
some_bih
Super User
Super User

Hi @Bassehave check link below as possible two solutions for your case, part

Same store sales with snapshot and 

model view  for 

FIGURE 6 The data model requires a weak MMR relationship between Date and StoreStatus.

 https://www.daxpatterns.com/like-for-like-comparison/ 

 

Did I answer your question? Kudos appreciated / accept solution





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors