Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
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 )
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
User | Count |
---|---|
23 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
7 | |
6 |