Forum Discussion
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...
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
But even this would concat whatever is in [Material] with the term customer. Wouldn´t that result in
| CustomerID | Material | NewColumn |
| 1 | ADV | ADV Customer |
| 1 | Viewer | Viewer Customer |
| 1 | Editor | Editor Customer |
| 1 | Whatever | Whatever Customer |
| 2 | ENT | ENT Customer |
| 2 | Viewer | Viewer Customer |
| 2 | Editor | Editor Customer |
| 2 | Whatever | Whatever 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.
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
Thank you so much, I wasn´t able to check earlier. That did the trick!