Return back max value where ID's are the same

[Anonymous]

Hi Experts

I ONLY want to return back the MAX value where two ids are the same and the ID with the smallest value to be blank...

Calculated column

Mearure =

VAR _maxvalue = calculate(MAX(Table[cost], Allexpect(Table,Table[SKID]))

VAR _Result = 
IF(Table[Conseq] = 0,

IF(_maxvaluue > 1000000, "VB3",
IF(_maxvaluue >= 100000 && (_maxvaluue <= 1000000, "VB2B",
IF(_maxvaluue >= 25000 && (_maxvaluue <= 100000, "VB3B","VB1")))).

the above measure is giving me max value for both ID's

ID	Cost	Current Restult	Expected Result
1253542	1000	1000	1000
1253542	50	1000

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try below dax formula:

Measure =
VAR cur_id =
    SELECTEDVALUE ( 'Table'[ID] )
VAR cur_cost =
    SELECTEDVALUE ( 'Table'[Cost] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[ID] = cur_id )
VAR max_cost =
    MAXX ( tmp, [Cost] )
VAR max_id =
    MAXX ( tmp, [ID] )
RETURN
    IF ( cur_id = max_id && cur_cost = max_cost, max_cost, BLANK () )

Add a table visual with this measure and column:

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.