Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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 Code | Store |
1 | C |
2 | B |
3 | A |
4 | A |
5 | B |
6 | A |
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 Code | Store |
1 | C |
2 | B |
3 | D |
4 | D |
5 | B |
6 | A |
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 Code | Store | Year |
1 | A | 2022 |
2 | B | 2022 |
3 | C | 2022 |
1 | D | 2023 |
2 | B | 2023 |
3 | C | 2023 |
Problem here is, now the Zip Code column is no longer unique. The other option is:
Zip Code | 2022 | 2023 |
1 | C | C |
2 | B | B |
3 | A | D |
4 | A | D |
5 | B | B |
6 | A | A |
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
Solved! Go to Solution.
Hi @Bassehave check link below as possible two solutions for your case, part
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
Proud to be a Super User!
Hi @Bassehave check link below as possible two solutions for your case, part
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
Proud to be a Super User!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
21 | |
21 | |
21 | |
13 | |
12 |
User | Count |
---|---|
43 | |
28 | |
25 | |
23 | |
22 |