Forum Discussion
Apply value based on multiple values within a single cell
Hello,
I am creating a marketing report that segments customers based on their interest.
Products fall broadly into three category types:
| Product | Category |
| Product 1, Product 3, Product 6 | Category 1 |
| Product 2, Product 4, Product 5 | Category 2 |
Chosen products from both Category 1 and Category 2 | Category 3 |
They can choose either a single product or a combination of any products. For example:
- A customer who chooses "Product 1" will be in "Category 1"
- A customer who chooses "Product 1" and "Product 3" will be in "Category 1"
- A customer who chooses "Product 2" will be in "Category 2"
- A customer who chooses "Product 1" and "Product 4" will be in "Category 3"
The data I extract only shows customer details and the product they are interested in:
| Customer | Product interest |
| Customer A | Product 1 |
| Customer B | Product 2, Product 3 |
| Customer C | Product 1, Product 5, Product 6 |
| Customer D | Product 4, Product 5 |
How can I show which category each customer belongs to based on their product interest?
Kind regards
Michael
Hi mike_newbs
Please use below DAX for calculated column (alter if you needs to change logic)Category = VAR _P1 = CONTAINSSTRING([Product interest], "Product 1") VAR _P2 = CONTAINSSTRING([Product interest], "Product 2") VAR _P3 = CONTAINSSTRING([Product interest], "Product 3") VAR _P4 = CONTAINSSTRING([Product interest], "Product 4") VAR _P5 = CONTAINSSTRING([Product interest], "Product 5") VAR _P6 = CONTAINSSTRING([Product interest], "Product 6") RETURN SWITCH( TRUE(), (_P1 = True || _P3 = True || _P6 = True) && NOT(_P2 = True || _P4 = True || _P5 = True),"Category 1", (_P2 = True || _P4 = True || _P5 = True) && NOT(_P1 = True || _P3 = True || _P6 = True),"Category 2", "Category 3")If solved your requirement, please mark this answer as SOLUTION.
If this comment helps you, hit the LIKE 👍
Thanks
Pijush
3 Replies
Hi mike_newbs
Please use below DAX for calculated column (alter if you needs to change logic)Category = VAR _P1 = CONTAINSSTRING([Product interest], "Product 1") VAR _P2 = CONTAINSSTRING([Product interest], "Product 2") VAR _P3 = CONTAINSSTRING([Product interest], "Product 3") VAR _P4 = CONTAINSSTRING([Product interest], "Product 4") VAR _P5 = CONTAINSSTRING([Product interest], "Product 5") VAR _P6 = CONTAINSSTRING([Product interest], "Product 6") RETURN SWITCH( TRUE(), (_P1 = True || _P3 = True || _P6 = True) && NOT(_P2 = True || _P4 = True || _P5 = True),"Category 1", (_P2 = True || _P4 = True || _P5 = True) && NOT(_P1 = True || _P3 = True || _P6 = True),"Category 2", "Category 3")If solved your requirement, please mark this answer as SOLUTION.
If this comment helps you, hit the LIKE 👍
Thanks
PijushMy pleasure mike_newbs
