Replace values based on measure

[Anonymous]

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.

[v-lili6-msft · Accepted solution]

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

[Anonymous]

Thanks! That worked.