How to find the latest note in the set of data

[nhodges]

Hi my data looks similar to this. I need a way to return the latest Note inputted per ID. So for example it would return the note "Bye" for ID: 1 because that was the last note entered based off of the time.

ID	Note	Date
1	Hi	2/23/2023 10AM
1	Hi	2/23/2023 10:01AM
1	Bye	2/23/2023 10:02 AM
2	Hi	2/23/2023 10AM
2	Hi	2/23/2023 10:01AM
3	Bye	2/23/2023 10AM

[Greg_Deckler]

@nhodges Try:

Measure =
  VAR __ID = MAX('Table'[ID])
  VAR __MaxDateTime = MAXX(FILTER(ALLSELECTED('Table'),[ID] = __ID),[Date])
  VAR __Result = MAXX(FILTER(ALLSELECTED('Table'),[ID] = __ID && [Date] = __MaxDateTime),[Note])
RETURN
  __Result

[nhodges]

It says my Syntac for VAR in correct: it is saying it cannot locate the field [id] I know I am in the right table: The measure is as follows:

lastmemo =

  VAR __ID = MAX('EDCA Data'[id])

  VAR __MaxDateTime = MAXX(FILTER(ALLSELECTED('EDCA Data',[id] = __ID),[memoDetails.memoDate])

  VAR __Result = MAXX(FILTER(ALLSELECTED('EDCA Data'),[id] = __ID && [memoDetails.memoDate] = __MaxDateTime),[memoDetails.memoDesc])

RETURN

  __Result

[Greg_Deckler]

@nhodges I think you are missing a )

lastmemo =
  VAR __ID = MAX('EDCA Data'[id])
  VAR __MaxDateTime = MAXX(FILTER(ALLSELECTED('EDCA Data'),[id] = __ID),[memoDetails.memoDate])
  VAR __Result = MAXX(FILTER(ALLSELECTED('EDCA Data'),[id] = __ID && [memoDetails.memoDate] = __MaxDateTime),[memoDetails.memoDesc])
RETURN
  __Result

[nhodges]

I just realized my ID field will not convert to a number. the format of the ID field is

ABC-12345-11345

ABC-12345-11346

So I think it is having trouble finding the max. Is there a way around this or should I just extract the numbers and put together in seperate column?

[Greg_Deckler]

@nhodges All depends on context. I was assuming that this would be used in the context where there would be only a single row for ID in context. Can you provide more information on how you are using this?

[nhodges]

Hi Greg, Sorry for all the confusion here is a snapshot of the fields I am working with. This Id spans many rows. I would want my solution for this to find the most recent date of that ID, then enter the note that is associated. (some dates repeat)

[FreemanZ · Accepted solution]

hi @nhodges 

try to plot a table visual with the ID column and a measure like:

LastMemo =

VAR _lastdate = MAX(TableName[memoDate])

RETURN

MAXX(

    FILTER(

        TableName,

        TableName[memoDate] = _lastdate

    ),

    TableName[memoDesc]

)

[nhodges]

Thank you! This works perfectly