Customized Column // Transform product names into categories

[LucaN123]

Hi 

I kindly ask for support regarding the below problem in Power BI. 

I have a column which lists the products sold in a specific project in one row like displayed below (Products combined). I would like to create another customized column which checks the products combined column and returns values based on the following: 

- If products combined column is empty, return empty

- If products combined column contains exactly "Product A,Product B", return Product A including B

- If products combined column contains at least two of the following words/products (Product A, Product B, Product C, Product D), return Solution Project

- Otherwise return value of Products combined column. 

I have tried to use the formula further down below, but apparently there is a mistake in the third IF statement and the result is not displayed correctly (see below in red).  Would be great if you could advise what the mistake is here? If there is an easier way to achieve the expected result, please let me know. Thank you!

ID	Products combined	Product Reporting (current result)	Product Reporting (expected result)
1	Product A	Product A	Product A
2	Product A,Product B	Product A including Product B	Product A including Product B
3	Product A,Product B,Product C, Product D	Product A,Product B,Product C, Product D	Solution Project
4	Product A,Product F	Product A,Product F	Product A,Product F

IF(
    ISBLANK('Dashboard'[ProductsCombined]),
    BLANK(),
    IF(
        'Dashboard'[ProductsCombined] = "Product A,Product B",
        "Product A including Product B",
        IF(
            CALCULATE(
                COUNTROWS(
                    FILTER(
                        VALUES('Dashboard'[ProductsCombined]),
                        COUNTROWS(
                            INTERSECT(
                                VALUES('Dashboard'[ProductsCombined]),
                                {"Product A", "Product B", "Product C", "Product D", "Product E", "Product F", "Product G"}
                            )
                        )
                    )
                )
            ) >= 2,
            "Solution Project",
            'Dashboard'[ProductsCombined]
        )
    )
)

Thanks in advance!

[Anonymous · Accepted solution]

Hi @LucaN123 ,

Try formula like below:

Conditional =
IF (
    Data[Products combined] = BLANK (),
    BLANK (),
    IF (
        Data[Products combined] = "Product A,Product B",
        "Project A including Project B",
        IF (
            CONTAINSSTRING ( Data[Products combined], "X" )
                || CONTAINSSTRING ( Data[Products combined], "Y" )
                || CONTAINSSTRING ( Data[Products combined], "F" ),
            Data[Products combined],
            IF (
                (
                    LEN ( Data[Products combined] )
                        - LEN ( SUBSTITUTE ( Data[Products combined], ",", "" ) )
                ) >= 1,
                "Solution Project",
                Data[Products combined]
            )
        )
    )
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kishore_KVN]

Hello @LucaN123 ,

Use below calculation to get the required solution:

Conditional = 
IF(Data[Products combined] = BLANK(),BLANK(),
IF(Data[Products combined] = "Product A,Product B","Project A including Project B",
IF((LEN(Data[Products combined])-LEN(SUBSTITUTE(Data[Products combined],",","")))>=2,"Solution",Data[Products combined])))

Your output looks as below:

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[LucaN123]

Hi @Kishore_KVN , Thank you for your swift response. 

Unfortunately, the proposed solution is not fully working. Based on my understanding, the formula returns "Solution Project" if the Products combined column includes more than two products, however, the goal is the following: 

I have a list of products [Product A, Product B, Product C, Product D] --> If the products combined column contains at least two of those products, the customized column should return "Solution Project". (Only exception is the previous if statement "Product A, Product B" --> customized column should return Product A including Product B). I have added some examples in blue where, based on this logic, "Solution Project" should be returned. But with the current formula, the examples in red are not shown correctly.

In the example for ID 8 (see below), "Solution Project" should NOT be returned as Product X &Y are not part of the list --> only, therefore, the value of the product combined column should be displayed. 

ID	Products combined	Product Reporting (current result)	Product Reporting (expected result)
1	Product A	Product A	Product A
2	Product A,Product B	Product A including Product B	Product A including Product B
3	Product A,Product B,Product C, Product D	Product A,Product B,Product C, Product D	Solution Project
4	Product A,Product F	Product A,Product F	Product A,Product F
5	Product B,Product C	Product B, Product C	Solution Project
6	Product C,Product D	Product C, Product D	Solution Project
7	Product B,Product C,Product D	Solution Project	Solution Project
8	Product A,Product X, Product Y	Solution Project	Product A,Product X, Product Y