Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
avalonds
Frequent Visitor

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.

 

Supersession Table 1.PNG

 

 

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.

 

Supersession Table 2.PNG

 

What would be the best way to tackle this problem?

Tables as text format below:

Dim-Product Table
Item code (key)Attributes
Product 1Blue
Product 2Green
Product 3Yellow
Product 4Purple
Product 5Blue
Product 6Black
Product 7Red
Product 8

Magenta

 

Dim-Product Changes Table
Item code (new)Item code (old)Date Changed
Product 1Product 41-Mar-18
Product 3Product 74-Feb-18
Product 4Product 830-Jun-17

 

Fact Sales Table
Order IDItem codeSalesSales Calendar Date
100Product 1$                     1001-May-18
102Product 3$                     3001-Jun-18
103Product 6$                     50023-Apr-18
112Product 6$                       303-Apr-18
113.3Product 7$                       2022-Dec-17
114.6Product 4$                       10Nov-17
115.9Product 8$                       4014-Feb-17

 

 

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@avalonds,

 

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-chuncz-msft
Community Support
Community Support

@avalonds,

 

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.