Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Customized column based on a certain row value

Dear community,

 

I am looking for a solution to flag/categorize all lines in a customer table in a "NewColumn". As soon as a customerID has a specific material, all lines relate to that customer should be flagged as visualized below:


CustomerIDMaterialNewColumn
1ADVADV Customer
1ViewerADV Customer
1EditorADV Customer
1WhateverADV Customer
2ENTENT Customer
2ViewerENT Customer
2EditorENT Customer
2WhateverENT Customer

 

Any help highly appreciated. 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    You can create a calculated column as below:

    NewColumn = 
VAR _ADVcount =
    CALCULATE (
        COUNT ( 'Customers'[CustomerID] ),
        FILTER (
            ALL ( 'Customers' ),
            'Customers'[CustomerID] = EARLIER ( 'Customers'[CustomerID] )
                && 'Customers'[Material] = "ADV"
        )
    )
VAR _ENTcount =
    CALCULATE (
        COUNT ( 'Customers'[CustomerID] ),
        FILTER (
            ALL ( 'Customers' ),
            'Customers'[CustomerID] = EARLIER ( 'Customers'[CustomerID] )
                && 'Customers'[Material] = "ENT"
        )
    )
RETURN
    IF (
        _ADVcount > 0,
        "ADV Customer",
        IF (
            _ENTcount > 0,
            "ENT Customer",
            CONCATENATE ( 'Customers'[Material], " Customer" )
        )
    )

    Best Regards

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI Anonymous .

    You can use 

    Custom = COMBINEVALUES(" ",'Sheet1 (2)'[Material],"Customer") .
     
    You can also use;
    CUSTOM = CONCATENATE('Sheet1 (2)'[Material],"Customer")
     
    If this post answers your question, then please mark it as 'Accept as Solution' so that others could find it easily.
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hey Anonymous ,

       

      thanks for answering here, your proposed solution results in something like this:

      CustomerIDMaterialNewColumn
      1ADVADV 1
      1ViewerViewer 1
      1EditorEditor 1
      1WhateverWhatever 1
      2ENTENT 2
      2ViewerViewer 2
      2EditorEditor 2
      2WhateverWhatever 2

       

      That is not what I am looking for. I really need all lines of a customer (with the same ID) where in one or more lines the material is ADV to be flagged as "ADV Customer".

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        You don't need to use Customer ID in here; 

        Custom = COMBINEVALUES(" ",'Sheet1 (2)'[Material],"Customer").

        It is a text and not a field.

        Copy my formula as it is and see if it works.