Forum Discussion
How to have dynamic slicers with a Slowly Changing Dimension table
Hello,
I have an issue about my dimension table: my Company table is a SCD. It has some fields that are changing in 2022. So I have two versions of my Company table: Coca cola Type is changing from "Soda" to "Juice" on the Type column.
On my PBI report, I have a 2 slicers with:
- The Date ("Date" column in the Date Table)
- Another slicer with the Type of the product ("Type" column in the Company Table)
How can i manage my tables so that when I select 2021 & Soda in my 2 slicers, I can see Coca cola datas in the Soda category
and when I select 2022, & Juice, I can see Coca cola datas in the Juice category ?
Note that I have a lot of fact tables like the Price table (around 20 facts tables like this)
Company2021 (dimension table that is a SCD changing in 2022)
| Index | Name | Type |
| Coca_Index | Coca Cola | Soda |
| Pepsi_index | Pepsi | Soda |
| Orange_Index | Orange Juice | Juice |
Company2022
| Index | Name | Type |
| Coca_Index | Coca Cola | Juice |
| Pepsi_index | Pepsi | Soda |
| Orange_Index | Orange Juice | Juice |
Price (fact table)
| Type | Price | Date |
| Coca_Index | 2.50 | 2021 |
| Pepsi_index | 2.50 | 2021 |
| Orange_index | 2 | 2021 |
| Coca_Index | 3 | 2022 |
| Pepsi_index | 3 | 2022 |
| Orange_index | 2 | 2021 |
Date (Dimension table)
| Date | index |
| 2021 | 1 |
| 2022 | 2 |
Do you know how can I deal with this issue ?
Thanks for the help !
Hi Anonymous ,
It is suggested to create a field like 'Year-Type'. When you select '2021-Soda' in the slicer, Coca Cola will appear, and then when you select '2022-Juice', Coca Cola will also appear.
Here's one of the solution.
1.Add year columns in the company tables.
2.Append company tables.
3.Merge the new appended table and the price table.
Expand the price column.
4.Add a calculated column in the merged table.
Year-Type = [Year]&"-"&[Type]The result:
Or one slicer is the year, and one slicer is the type, which can also filter to Coca Cola.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Syndicate_AdminAdministrator
Typically in data when you have values changing over time your best bet is to add start/end effective dates for the table values, so that you can link a combination of the date and key field to pull the appropriate results. You can fudge that for the other 99% of the table entries by assigning dates of 1/1/1900 and 12/31/2099 for the null values, and just add the new row to the tables where values have changed with a cutoff on the old value and an inception on the new. Then you'll build a composite key that's the date of the transaction/activity and the key value to get the different results.
If history isn't a big deal you can keep everything at the product level and build your categorical roll ups on the fly according to the ever changing table. You'll see performance history for "Coca_Index" and just lose the context of performance under Soda to have it as if it was always under juice. If you are constantly rejuggling rollups that's probably the least painful once your users understand that they inherit the product history along with the product in their Company assignment.
Hi Anonymous ,
It is suggested to create a field like 'Year-Type'. When you select '2021-Soda' in the slicer, Coca Cola will appear, and then when you select '2022-Juice', Coca Cola will also appear.
Here's one of the solution.
1.Add year columns in the company tables.
2.Append company tables.
3.Merge the new appended table and the price table.
Expand the price column.
4.Add a calculated column in the merged table.
Year-Type = [Year]&"-"&[Type]The result:
Or one slicer is the year, and one slicer is the type, which can also filter to Coca Cola.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.