How to get the row in a table with the latest createdon date?

[wpf_]

I have a table in a variable:

Var Table1 =

Filter(

summarize(

Table1,

requestNum

startDate,

endDate,

employeeName,

createdOn,

),

employeeName = "John Smith" &&

AND (

Table1[startDate]  <= TodayDate,

Table1[endDate] >= TodayDate

)

The result i get:

requestNum	startDate	endDate	employeeName	createdOn
req4001	6/22/2021	7/4/2021	John Smith	6/22/2021
req4040	6/28/2021	7/1/2021	John Smith	6/28/2021
vac6010	6/27/2021	7/3/2021	John Smith	5/24/2021

Then I have another variable that would get me the max requstNum i want:

VAR maxReqNum = MAXX(Table1, Table1[requestNum])

However it is getting me vac6010, but what I really want is req4040, based on the latest createdOn date.

How can i filter that table to give me the row which is created last, or how can i grab the latest created requestNum?  Thanks 

[AlB · Accepted solution]

Hi @wpf_ 

VAR Table1 = //... the code you already have and then:

VAR latestCreatedOn_ = MAXX(Table1, Table1[createdOn]) //Extract latest date
VAR auxT_ = FILTER(Table1, Table1[createdOn] = lastestCreatedOn_) //Get row with latest createdOn date
VAR maxReqNum_ = MAXX(auxT_, Table1[requestNum]) //Extract requestNum for that row 
RETURN
maxReqNum_

Do note auxT_ should be a one-row table so using MINX at maxReqNum_ would also work. It's just a way to extract the value in the Table1[requestNum] columns

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[wpf_]

@AlB 

would this be ok?

maxx(filter(table1, tbale1[createdOn] = lastestCreatedOn_), table1[requestNum])

would it be faster to break it up like you did?

thanks

[AlB]

@wpf_ 

Sure. That is fine. I just split it in more steps above so that it was easier to follow.

As for the performance, I don't think there'd be a significant difference but it'd be a matter of checking with Dax Studio

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[wpf_]

@AlB Hi, can you help me with this?  https://community.powerbi.com/t5/Desktop/Why-If-else-statemet-is-showing-both-results/m-p/1946253#M7...

It's a continuation of the formula you helped me with. THanks. 

[wpf_]

@AlB thanks.  

[amitchandak]

@wpf_ ,  Based on what I got,

You have to use measures like one for other columns

last requestNum=
VAR __id = MAX ('Table'[employeeName] )
VAR __date = CALCULATE ( Min('Table'[createdOn] ), ALLSELECTED ('Table' ), 'Table'[employeeName] = __id )
CALCULATE ( MAx ('Table'[requestNum] ), VALUES ('Table'[employeeName] ),'Table'[employeeName] = __id,'Table'[createdOn] = __date )