Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 sa...
  • v-lili6-msft's avatar
    7 years ago

    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