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 s...
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

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.

 

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

But even this would concat whatever is in [Material] with the term customer. Wouldn´t that result in 

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



I need a solution where the term "ADV Customer" is applied to all lines for a certain customer as soon one (or more) of the materials is ADV.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you so much, I wasn´t able to check earlier. That did the trick!