topic Equivalent of max_by() in DAX? in DAX Commands and Tips

Equivalent of max_by() in DAX?

crispybc — Thu, 22 Jun 2023 15:50:39 GMT

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?

Re: Equivalent of max_by() in DAX?

OwenAuger — Fri, 23 Jun 2023 03:30:02 GMT

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

Re: Equivalent of max_by() in DAX?

devanshi — Fri, 23 Jun 2023 06:26:18 GMT

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 )