Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Return back max value where ID's are the same

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

IDCostCurrent RestultExpected Result
1253542100010001000
1253542501000 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.