Unable to solve, product code mapping related problem.

Question

I have common problem however unable to solve using power query or DAXHave a sales table in SQL which has product code, country of sales, day of transaction and sales amount, doesnt cotain product name. For product name we have product code mapping table in excel.&nbsp;Issue is after certain date the product names gets changed. We need to plot product name wise sales with the dates.&nbsp;Sales tableCountryProduct CodeDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;SalesIndia32562/1/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8,023China955812/1/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,541India95582/1/2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7,058China95589/1/2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8,165India95589/1/2021&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,739China955810/1/2021&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7,343India325611/1/2021&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7,519China32564/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,944India325611/1/2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7,787China95581/1/2023&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8,060India95582/1/2023&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,400&nbsp;Mapping Table in Excel&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Product CodeProduct nameProduct name after 9/1/20219558Product AProduct M3256Product CProduct N&nbsp;Would you please help me solve this.Thanks

some_bih · Answer

Hi&nbsp;Anonymous&nbsp;you need to create relationship in Power BI to connect your to tables (by Product code) to leverage it later using measureCheck link for thishttps://learn.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships&nbsp;&nbsp;Maintening product names in Product table should resolve your issues.Did I answer your question? Mark my post as a solution! Kudos Appreciated!

anonymous · Answer

Hi&nbsp;some_bih, Thank you for promt response, I have already created relationship between tables, however challenges comes while writing measures because of changing names after certain dates. Could you share your Dax to resolve the issue. Thanks

some_bih · Answer

Hi&nbsp;Anonymous&nbsp;if you want to trace changing names for product you should provide inputs like StarDate and EndDate for each product code, depending on inputs (modeling this part in overall model) solution could be achived.The best would be that in separate process you check if product is changed name and decide what is product name for current /alwasy and that value consider in model.

Forum Discussion

Unable to solve, product code mapping related problem.

3 Replies

Related content

Logic problem?

Problem calculating production rate

map problem

Slider problems

Production Lessons

Country	Product Code	Date	Sales
India	3256	2/1/2019	8,023
China	9558	12/1/2019	6,541
India	9558	2/1/2020	7,058
China	9558	9/1/2020	8,165
India	9558	9/1/2021	6,739
China	9558	10/1/2021	7,343
India	3256	11/1/2021	7,519
China	3256	4/1/2022	6,944
India	3256	11/1/2022	7,787
China	9558	1/1/2023	8,060
India	9558	2/1/2023	6,400

Product Code	Product name	Product name after 9/1/2021
9558	Product A	Product M
3256	Product C	Product N