Equivalent of max_by() in DAX?

[crispybc]

In some SQL there is a function max_by(maxed_value, return_value). It basically finds the maximum value of a column (maxed_value) but then returns the value of column (return_value) of the matching row.

Is there an equivalant in DAX, or what is the most efficient way to reproduce the same?

[devanshi]

In Dax function ,  MAX function is used to find maximum of value following is the formula to find maximim value :
 
MaxByReturn =
VAR MaxValue = MAX(Table[maxed_value])
RETURN
CALCULATE( MAX(Table[return_value]), Table[maxed_value] = MaxValue )

[OwenAuger]

Hi @crispybc 

From the documentation on MAX_BY, I see that in the case of multiple matching rows, an arbitrary value from the return_value column is returned. In the below code, I have used MAX to return such an arbitrary value.

Option 1: If return_value is never blank, a close equivalent could be to use LASTNONBLANKVALUE:

LASTNONBLANKVALUE (
    YourTable[maxed_value],
    MAX ( YourTable[return_value] )
)

Note that CALCULATE is not required for the 2nd argument due to automatic 

Option 2: You could write this, which would allow for blank values of return_value:

CALCULATE (
    MAX ( YourTable[return_value] ),
    LASTNONBLANK ( YourTable[maxed_value], 0 ) -- filter corresponding to max value
)

There are certainly other ways to formulate this but these are the most straightforward that come to mind.

Regards