Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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:
CustomerID | Material | NewColumn |
1 | ADV | ADV Customer |
1 | Viewer | ADV Customer |
1 | Editor | ADV Customer |
1 | Whatever | ADV Customer |
2 | ENT | ENT Customer |
2 | Viewer | ENT Customer |
2 | Editor | ENT Customer |
2 | Whatever | ENT Customer |
Any help highly appreciated.
Solved! Go to Solution.
Hi @Alesch ,
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
HI @Alesch .
You can use
Hey @SanketBhagwat ,
thanks for answering here, your proposed solution results in something like this:
CustomerID | Material | NewColumn |
1 | ADV | ADV 1 |
1 | Viewer | Viewer 1 |
1 | Editor | Editor 1 |
1 | Whatever | Whatever 1 |
2 | ENT | ENT 2 |
2 | Viewer | Viewer 2 |
2 | Editor | Editor 2 |
2 | Whatever | Whatever 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".
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.
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 @Alesch ,
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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
79 | |
64 | |
52 | |
46 |
User | Count |
---|---|
213 | |
89 | |
77 | |
66 | |
60 |