cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wpf_
Post Prodigy
Post Prodigy

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

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:
 
requestNumstartDateendDateemployeeNamecreatedOn
req40016/22/20217/4/2021John Smith6/22/2021
req40406/28/20217/1/2021John Smith6/28/2021
vac60106/27/20217/3/2021John Smith5/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 
 
 
 
 
1 ACCEPTED SOLUTION
AlB
Super User III
Super User III

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

   

SU18_powerbi_badge

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.

 

View solution in original post

6 REPLIES 6
AlB
Super User III
Super User III

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

   

SU18_powerbi_badge

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.

 

View solution in original post

wpf_
Post Prodigy
Post Prodigy

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

@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

 

SU18_powerbi_badge

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_
Post Prodigy
Post Prodigy

@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_
Post Prodigy
Post Prodigy

@AlB thanks.  

amitchandak
Super User IV
Super User IV

@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 )

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors