Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
crispybc
Frequent Visitor

Equivalent of max_by() in DAX?

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?

2 REPLIES 2
devanshi
Helper V
Helper V

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
Super User
Super User

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

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.