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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Latest datetimestamp by id

Hi,

 

I have 4 columns
ID Name         Datetimestamp                Value
1   BMI     2020/08/04 12:23:12               12
1   BMI     2020/08/11 08:22:10               15
2   BMI     2020/08/11 12:25:12               20
3   BMI     2020/08/11 12:26:12               22
4   BMI     2020/08/11 12:21:12               13
4   BMI     2020/08/11 12:23:12               14


Output:

ID Name         Datetimestamp                Value
1   BMI     2020/08/11 08:22:10               15
2   BMI     2020/08/11 12:25:12               20
3   BMI     2020/08/11 12:26:12               22
4   BMI     2020/08/11 12:23:12               14

 

I need to see only latest record by ID using DAX . 

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@Anonymous 

you can also create a new table

Table 2 = ADDCOLUMNS(SUMMARIZE('Table','Table'[ID],'Table'[Name ],"datetimestamp",MAX('Table'[Datetimestamp ])),"value",MAXX(FILTER('Table','Table'[Datetimestamp ]=[datetimestamp]),'Table'[Value]))

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
FrankAT
Community Champion
Community Champion

Hi @Anonymous ,

take a look at the following solution:

 

11-08-_2020_18-08-51.png

 

Last DateTimeStamp = LASTNONBLANKVALUE('Table'[Datetimestamp],MAX('Table'[Datetimestamp]))

Value that belongs to Last = LASTNONBLANKVALUE('Table'[Datetimestamp],MAX('Table'[Value]))

 

Regards FrankAT

ryan_mayu
Super User
Super User

@Anonymous 

you can also create a new table

Table 2 = ADDCOLUMNS(SUMMARIZE('Table','Table'[ID],'Table'[Name ],"datetimestamp",MAX('Table'[Datetimestamp ])),"value",MAXX(FILTER('Table','Table'[Datetimestamp ]=[datetimestamp]),'Table'[Value]))

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User
Super User

@Anonymous , use these two with ID and name in a visual table

Value new lastnonblankvalue(Table[Datetimestamp], max(Table[Value]))
Datetimestamp new = lastnonblank(Table[Datetimestamp], blank())

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Greg_Deckler
Super User
Super User

@Anonymous - Seems like you want a selector like a Complex Selector, https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

 

In your case it should be along the lines of:

 

Selector = 

  VAR __Date = MAX('Table'[Date])

  VAR __Name = MAX('Table'[Name])

  VAR __MaxDate = MAXX(FILTER(ALL('Table'),[Name]=__Name),[Date])

RETURN

  IF(__Date = __MaxDate,1,0)

 

You can thing filter using Selector.

  IF(



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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