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 ...
Need Help
  • 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.

Kishore_KVN's avatar
Kishore_KVN
Icon for Solution Sage rankSolution Sage
2 years ago

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
    2 years ago

    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