cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mikejarod Regular Visitor
Regular Visitor

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:

customercar
johnaudi
johnvw
johnhyundai
petetesla
sarahjaguar
sarahvolvo

 

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:

 

customeramount of cars
john3
sarah2
owners with only one car1

 

This should behave dynamically and support crossfiltering from other objects.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Replace values based on measure

hi,  @mikejarod 

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:

6.JPG

By the way, you could also add or adjust the conditional of the measure.

 

here is pbix file, please try it.

 

Best Regards,
Lin

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Replace values based on measure

hi,  @mikejarod 

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:

6.JPG

By the way, you could also add or adjust the conditional of the measure.

 

here is pbix file, please try it.

 

Best Regards,
Lin

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mikejarod Regular Visitor
Regular Visitor

Re: Replace values based on measure

Thanks! That worked.