How to have a flexible dimension table

[Anonymous]

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 !

[BA_Pete]

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

[Anonymous]

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...

[BA_Pete]

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

[Anonymous]

Hello @BA_Pete 

I have created another topic that suits better my use case. Could you please check it and think again about it to check if you would still recommend me the same solution ?
Here is the link: https://community.powerbi.com/t5/Desktop/Slicer-with-a-Slowly-changing-dimension-table/m-p/2453863#M...

Thank you very much again for your answers Pete, it helps a lot !