Forum Discussion
How to have a flexible dimension table
Hello,
I have an issue about my dimension table: my Company table has some fields that are changing in 2022, and i would like my slicer of the company name to display the name of the company, depending on the date. For example, I want to see "Coca Cola" if the user select 2021, and "Coca Cola Company" if the user select 2022, here is an example of the structure of my datas:
Company2021
| Name | Type |
| Coca Cola | Soda1 |
| Pepsi | Soda2 |
Company2022
| Name | Type |
| Coca Cola Company | Soda1 |
| Pepsi | Soda2 |
Price
| Type | Price | Date |
| Soda1 | 2.50 | 2021 |
| Soda2 | 2.50 | 2021 |
| Soda1 | 3 | 2022 |
| Soda2 | 3 | 2022 |
Date
| Date | index |
| 2021 | 1 |
| 2022 | 2 |
Do you know how can I deal with this issue ?
Thanks for the help !
4 Replies
Hi Anonymous ,
I'm assuming that your fact tables only hold a company code, and that the company code is not changing, just the company description in the dimension table.
As such, I think maybe the simplest way to handle this would be to amend the company code in your fact tables using logic such as:
if [companyCode] = "COCA" and [factTableDate] <= #date(2021, 12 ,31) then "COCA21" else [companyCode]You can implement this either using a new column or a conditional replace step.
Once you've updated all the relevant fact tables in your model, then you would add a new row into your company dimension table, something like this:
Table.InsertRows(previousStepName, 0, {[ companyCode = "COCA21", companyDescription = "Coca Cola" ]} )This will allow you to relate your dimension table correctly to all of your fact tables and display different versions of [companyDescription] depending on the fact table date.
Pete
Hello BA_Pete, thank you very much for the answer.
I have understood your solution, and I think it works too.
Nevertheless there is a problem, I have a lot of fact tables and it would be very long to do this. Do you have an alternative solution when i have too much fact tables ?
I tried on my side to duplicate my Company table and add a date column. In my final company table there will be something like this:Name Type Date Coca Cola Soda1 2021 Pepsi Soda2 2021 Coca Cola Company Soda1 2022 Pepsi Soda2 2022 but i am facing issues when i try to join the company table and the Date table. I don't even know if it is possible...
Hi Anonymous ,
Regarding the quantity of fact tables, I think you'll just need to take the pain on this if you're determined to do it. What you're trying to do isn't generally standard practice. If an organisation wants to change company details and wants the change to remain distinctly different from the historical position, then they would normally set up a new company code. In only changing the dimension description, and not creating a new, distinct, company/code, it seems as though your organisation doesn't want there to be a historical distinction, and is happy for the history to be updated with the new description. Obviously, the other option is to speak to your organisation and explain that they should set up a new company entry if they want the new one to be distinct from the old one.
Regarding the date table thing, I'm not sure I understand what you're trying to do. I think you should be looking to create a STAR schema model from your tables, rather than try and amalgamate/fatten dimension tables:
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
Pete
