How to get the last row of each ID using measure or filter based on date and time stamp

[Anonymous]

Hi Guys,

Newbie here,

May I ask how to get the last row of each ID in another table visual.

This is a streaming dataset so I cant manipulate the table. 

So in the table above, I only want to show the row which contains the last task of each employee ID  which should be the END for 10736211 and MEETING for 10698442.

Thank you guys for answering!

[vivran22 · Accepted solution]

Hello @Anonymous ,

You may try:

Latest Status = 
VAR _LatestTime = 
    MAX('Table'[Date])

VAR _FilterTable = 
    FILTER(
        'Table',
        'Table'[RecordTimestamp] = _LatestTime
    )

VAR _LatestStatus = 
    CALCULATE(
        VALUES('Table'[Task]),
        _FilterTable
    )

RETURN

_LatestStatus

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[vivran22 · Accepted solution]

Hello @Anonymous ,

You may try:

Latest Status = 
VAR _LatestTime = 
    MAX('Table'[Date])

VAR _FilterTable = 
    FILTER(
        'Table',
        'Table'[RecordTimestamp] = _LatestTime
    )

VAR _LatestStatus = 
    CALCULATE(
        VALUES('Table'[Task]),
        _FilterTable
    )

RETURN

_LatestStatus

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[parry2k]

@Anonymous try following measure.

Recent Task = 
VAR __maxDate = CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Employee] ) ) 
RETURN
CALCULATE ( MAX ( 'Table'[Task] ), ALLEXCEPT ( 'Table', 'Table'[Employee] ), 'Table'[Date] = __maxDate ) 

I would ? Kudos 🙂 if my solution helped. ?

[amitchandak]

@Anonymous ,

Try a new measure like this add to your table

Measure =
VAR __id = MIN ( 'Table'[EmployeeID] )
VAR __date = CALCULATE ( MIN( 'Table'[Date] ), ALLSELECTED ( 'Table' ), 'Table'[EmployeeID] = __id )
RETURN CALCULATE ( sum ( 'Table'[EmployeeID] ), VALUES ( 'Table'[EmployeeID] ), 'Table'[EmployeeID] = __id, 'Table'[Date] = __date )

[Anonymous]

Hi @amitchandak,

You've got the correct result that I wanted however, this will show the first task not the last. I want to show the last task for each ID.

[amitchandak · Accepted solution]

@Anonymous ,

Change all min with max

Measure =
VAR __id = MAX( 'Table'[EmployeeID] )
VAR __date = CALCULATE ( MAX( 'Table'[Date] ), ALLSELECTED ( 'Table' ), 'Table'[EmployeeID] = __id )
RETURN CALCULATE ( sum ( 'Table'[EmployeeID] ), VALUES ( 'Table'[EmployeeID] ), 'Table'[EmployeeID] = __id, 'Table'[Date] = __date )

[Anonymous]

Hi @amitchandak ,

I am experiencing another problem by using your given measure, If I add same task . the measure will give 2 outputs. It should be one and only the latest one. Please see the screenshot below.

It should be like this:

Thank you so much for answering @amitchandak ! I really appreciate it !

[Anonymous]

Hi @amitchandak,

This is great. Thank you for this!

Kudos!