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.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors