Forum Discussion
Changes in item code
I have the following tables in powerbi as noted below. Some products in my Dim-product table have changed the item code over the years to be a different item code. This is seen in the Dim-Product changes table which is a stand alone table. Also have a Dim-Date table that connects to my Facts Sales table.
My goal is to create a slicer that allows the user to see sales of products either with the changes or without the changes to the item code. Here's how the solution would look like. Ideally my fact sales tables has other attributes such as units and Gross margin as well and would like the slicer to work with these attributes as well.
What would be the best way to tackle this problem?
Tables as text format below:
| Dim-Product Table | |
| Item code (key) | Attributes |
| Product 1 | Blue |
| Product 2 | Green |
| Product 3 | Yellow |
| Product 4 | Purple |
| Product 5 | Blue |
| Product 6 | Black |
| Product 7 | Red |
| Product 8 | Magenta |
| Dim-Product Changes Table | ||
| Item code (new) | Item code (old) | Date Changed |
| Product 1 | Product 4 | 1-Mar-18 |
| Product 3 | Product 7 | 4-Feb-18 |
| Product 4 | Product 8 | 30-Jun-17 |
| Fact Sales Table | |||
| Order ID | Item code | Sales | Sales Calendar Date |
| 100 | Product 1 | $ 100 | 1-May-18 |
| 102 | Product 3 | $ 300 | 1-Jun-18 |
| 103 | Product 6 | $ 500 | 23-Apr-18 |
| 112 | Product 6 | $ 30 | 3-Apr-18 |
| 113.3 | Product 7 | $ 20 | 22-Dec-17 |
| 114.6 | Product 4 | $ 10 | Nov-17 |
| 115.9 | Product 8 | $ 40 | 14-Feb-17 |
5 Replies
Would really help to get to an answer if you pasted in your source data as text.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Thanks Greg. I've updated the initial post to include tables. Hope this helps!
I think that what you need to do is to make your product change table inclusive. By that I mean, you need to have it so that you would add records for:
Product 1 -> Product 1
Product 6 -> Product 6
If you had it that way, then you could use the Item ID column from this table in your first result table and the Item ID column from your fact table in your second result table. This would give you the intended effect of seeing it with or without changes. You could then create a button or two that would be tied to bookmarks to switch between the two views of the data.
