Calculate MAX text value

[ggzmorsh]

I have Table1 which contains 4 occurrences of the same ID happening in different date/times and remarks.

What i need to do is to return the most recent remarks which is "A". I tried using max value however it is only returning me the sorted alphabet which is "D" and i also used lookup value to return the last remark from the max date however remark "B" and "C" was updated at the same time.

ID	Date	Remarks
ABC123	01/06/2021 12:31:18	B
ABC123	01/07/2021 12:31:18	C
ABC123	01/08/2021 12:31:19	D
ABC123	01/09/2021 12:31:20	E
ABC123	01/10/2021 12:31:18	A

[v-yiruan-msft · Accepted solution]

Hi @ggzmorsh ,

You can create a measure as below:

Latest remark = 
VAR _maxdate =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
RETURN
    CALCULATE (
        MAX ( 'Table'[Remarks] ),
        FILTER (
            'Table',
            'Table'[ID] = MAX ( 'Table'[ID] )
                && 'Table'[Date] = _maxdate
        )
    )

Best Regards

[ggzmorsh]

This is the closest solution from what i have. However since we using the MAX in the dax that means if there is two dates with the same value it will return the sorted remarks from Z-A. This brings light! thank you.

[amitchandak]

@ggzmorsh , Try measure like

lastnonblankvalue(Table[Date],Max(Table[Remark]))

or

calculate(lastnonblankvalue(Table[Date],Max(Table[Remark])), allexpect(Table, Table[ID]))

[ggzmorsh]

My dear, I have used the formula 

Last Remark = LASTNONBLANKVALUE(Table1[Date],MAX(Table1[Remark]))

which worked on a smaller scale however when used with a table which contains more than 15 million rows it is returning me wrong data.

[ggzmorsh]

I have tried your solution and it worked at the topic at hand. I will mark it as an accepted solution once i test it to a larger data and verify the results.

[Gabriel_Walkman]

How about like this? Pull in a visual filter for Date and use Top 1.

[ggzmorsh]

I needed it as a dax column.