Categorize based on other products

[Anonymous]

Hello everyone, I couldn't find a similiar topic for my problem.

I have sales data from store A which has sales from our products and from our competitors. Store A has their products categorized in a certain way which is not compatible with our categorization. Luckily, I have the categorization for our products so I can recategorize them but can't do that to the competitor products without a lot of grinding and manual work.

So here is my problem. I want to tell DAX to see if the "New Category" column is blank. If it is, search for "product" that has "New Category" and is the same value for the current row in "Old Category" (example, both are categorized as 1 in old category). Then, put value of new New Category (if previously they were both 1 now both of them should be 1A).

I want:

Old Category	Product	New category
1	Competitor A
1	Our product A	1A
2	Competitor B
2	Our product B	2A

 to look like 

Old Category	Product	New category	Calc column
1	Competitor A		1A
1	Our product A	1A	1A
2	Competitor B		2A
2	Our product B	2A	2A

Thanks!

[AlB · Accepted solution]

Hi @Anonymous 

Calc Column = CALCULATE(MAX(Table1[New category]);ALLEXCEPT(Table1;Table1[Old Category])) 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[Anonymous]

That makes sense. I haven't thought about it that way. Thanks for the blazing fast answer!