Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Customized Column // Transform product names into categories

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!

 

IDProducts combinedProduct Reporting (current result)Product Reporting (expected result)
1Product AProduct AProduct A
2Product A,Product BProduct A including Product BProduct A including Product B
3Product A,Product B,Product C, Product DProduct A,Product B,Product C, Product DSolution Project
4Product A,Product FProduct A,Product FProduct 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's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    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.

3 Replies

  • Hello Anonymous ,

    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!!

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      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. 

       

       

      IDProducts combinedProduct Reporting (current result)Product Reporting (expected result)
      1Product AProduct AProduct A
      2Product A,Product BProduct A including Product BProduct A including Product B
      3Product A,Product B,Product C, Product DProduct A,Product B,Product C, Product DSolution Project
      4Product A,Product FProduct A,Product FProduct A,Product F
      5Product B,Product CProduct B, Product CSolution Project
      6Product C,Product DProduct C, Product DSolution Project
      7Product B,Product C,Product DSolution ProjectSolution Project
      8Product A,Product X, Product YSolution Project Product A,Product X, Product Y
  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Anonymous ,

     

    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.