topic Re: Changes in item code in Developer

Changes in item code

avalonds — Fri, 27 Jul 2018 17:05:57 GMT

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

Re: Changes in item code

Greg_Deckler — Fri, 27 Jul 2018 16:13:40 GMT

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

Re: Changes in item code

avalonds — Fri, 27 Jul 2018 17:05:19 GMT

Thanks Greg. I've updated the initial post to include tables. Hope this helps!

Re: Changes in item code

Greg_Deckler — Fri, 27 Jul 2018 17:44:52 GMT

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.

Re: Changes in item code

avalonds — Fri, 27 Jul 2018 19:13:47 GMT

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?

Re: Changes in item code

v-chuncz-msft — Fri, 03 Aug 2018 09:23:47 GMT

@avalonds,

You may use Parent and Child Functions. Here is an article for your reference.