Custom column groupings

[Anonymous]

Hi,

I've got a column called "Products" that contains all sorts of products - for instance:

Lounge Chair

Lounge Chair Cushion, Red

Lounge Chair Cushion, Blue

Long Bench 160

Long Bench 160 Cushion

Is it possible to create a new column that groups "related" products together within the same "master product", like in the example below.

Product                                      Master Product

Lounge Chair                                       Lounge Chair

Lounge Chair Cushion, Red                 Lounge Chair

Lounge Chair Cushion, Blue                Lounge Chair

Long Bench 160                                   Long Bench

Long Bench 160 Cushion                     Long Bench

I hope that you can help 😉

[Anonymous]

@selimovd @amitchandak @Singaravelu_R 

Thank you for your replies.

Is it also this solution that I should use, even though i've got about 500-1000 products and probably have to create about 200 master products?

Or is there some way where I can just "remove" cushion (and potentially color) from the equation?

[Singaravelu_R · Accepted solution]

Hi @Anonymous 

In the query editor duplicate the column Product and by using split column by space

close and save the query editor then in the data section create calcuate column 

Master Product = 'Table'[Product - Copy.1] &" "& 'Table'[Product - Copy.2]

[selimovd]

With the SEARCH function that @amitchandak proposed you search for "Lounge Chair" and categorize it as such. If it's "Lounge Chair" or "Lounge Chair red" or "Lounge Chair with cushion" doesn't matter.

The search function will then categorize all as Lounge Chair.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi

[Singaravelu_R]

create new calculated column. 

Master Product=

Switch( true(),
search("Lounge Chair",[Product],,0) >0 , "Lounge Chair",
search("Long Bench",[Product],,0) >0 , "Long Bench",
"Other"
)

[selimovd]

Hello @Anonymous ,

yes that's possible.

You can either group them with new calculated column. Then you can check if Product contains "Lounge Chair" then it should be "Lounge Chair". Check the SWITCH/TRUE combination for that case:

DAX - The Diabolical Genius of “SWITCH TRUE” | P3 (powerpivotpro.com)

Or you can click with the mouse button to the column "Product" and chose "New group". There you can group easier by just combining the results to specific groups together:

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi

[amitchandak]

@Anonymous , Create a new column like

Switch( true(),
search("Lounge Chair",[Product],,0) >0 , "Lounge Chair",
search("Long Bench",[Product],,0) >0 , "Long Bench",
"Other"
)