Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
You may use Parent and Child Functions. Here is an article for your reference.
You may use Parent and Child Functions. Here is an article for your reference.
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.
Thanks. I think in your solution you meant Product 5 instead of Product 1?
Even if I made the table inclusive, it wouldn't solve the issue where product 8 changed to product 4 in 2017 and then to product 1 in 2018.
If I were to use the product changes table in this case, it would show me sales for product 1 and product 4 filtered for 2017 and 2018 dates. It may even put product 4 sales in product 1 and repeat product 4 sales again given it's a seperate line item. Is there another way I can adjust my tables to account for this issue of multiple product changes?
User | Count |
---|---|
16 | |
11 | |
5 | |
4 | |
3 |