Forum Discussion
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 .
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]))
4 Replies
ryan_mayuSuper 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]))- FrankAT
Community Champion
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
- 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(
- 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())
