Forum Discussion
Replace values based on measure
I think this is a variant on "TOP N with Others" without the TOP N but I can't seem to find the solution
Dataset:
| customer | car |
| john | audi |
| john | vw |
| john | hyundai |
| pete | tesla |
| sarah | jaguar |
| sarah | volvo |
Pete has only one car
I want to make a new column like customer, but in the case of pete (only one record) I want a custom text like "owners with only one car".
Ultimately this will be the output:
| customer | amount of cars |
| john | 3 |
| sarah | 2 |
| owners with only one car | 1 |
This should behave dynamically and support crossfiltering from other objects.
hi, Anonymous
I've had a case like "top n and others" before
https://community.powerbi.com/t5/Desktop/Top-N-and-Others-monthly/m-p/490708#M228672
For your case, "owners with only one car" must be defined in a column first.
Because measure is an aggregate value and dynamic based on the row context,
Then use this formula to create a measure
Measure = VAR _table = SUMMARIZE ( ALLSELECTED ( Table1 ), Table1[customer], "_countcar", CALCULATE ( COUNTA ( Table1[car] ) ) ) RETURN IF ( SELECTEDVALUE ( Specialties[customer] ) = "owners with only one car", CALCULATE ( COUNTAX ( FILTER ( _table, [_countcar] = 1 ), [customer] ) ) + 0, IF ( [countcar] <> 1, [countcar] ) )Result:
By the way, you could also add or adjust the conditional of the measure.
here is pbix file, please try it.
Best Regards,
Lin
2 Replies
v-lili6-msftCommunity Support
hi, Anonymous
I've had a case like "top n and others" before
https://community.powerbi.com/t5/Desktop/Top-N-and-Others-monthly/m-p/490708#M228672
For your case, "owners with only one car" must be defined in a column first.
Because measure is an aggregate value and dynamic based on the row context,
Then use this formula to create a measure
Measure = VAR _table = SUMMARIZE ( ALLSELECTED ( Table1 ), Table1[customer], "_countcar", CALCULATE ( COUNTA ( Table1[car] ) ) ) RETURN IF ( SELECTEDVALUE ( Specialties[customer] ) = "owners with only one car", CALCULATE ( COUNTAX ( FILTER ( _table, [_countcar] = 1 ), [customer] ) ) + 0, IF ( [countcar] <> 1, [countcar] ) )Result:
By the way, you could also add or adjust the conditional of the measure.
here is pbix file, please try it.
Best Regards,
Lin