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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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