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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

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.

 

@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

 

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

 

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

@AlB thanks.  

amitchandak
Super User
Super User

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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