Latest datetimestamp by id

[Anonymous]

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 . 

[FrankAT]

Hi @Anonymous ,

take a look at the following solution:

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

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

Regards FrankAT

[ryan_mayu · Accepted solution]

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

[amitchandak]

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

[Greg_Deckler]

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