Calculate Max to Return Text

[ggzmorsh]

I have Table1 which contains 4 ID occurrences in 4 different dates/times and 4 different remarks. What i wanted to do is to return the last occurring remark from the last date or most recent.

I tried using a simple MAX value to return the last remark however it is only returning me an alphabetically order result which is "D". I wanted to return "B" however lookup value does not work for me because the date/time has a duplicate(A,B). 

ID	Date	Remarks
123ABC	01/01/2020 09:22:00	D
123ABC	01/01/2020 09:22:23	C
123ABC	01/01/2020 10:15:47	A
123ABC	01/01/2020 10:15:47	B

[littlemojopuppy]

Hi @ggzmorsh.  Try this...

Most Recent Remark:=
	VAR	MostRecentRemarkDateTime =
		CALCULATE(
			LASTNONBLANK(
				Remarks[DateTime],
				TRUE()
			),
			ALLEXCEPT(
				Remarks,
				Remarks[ID]
			)
		)
	RETURN
	
	LOOKUPVALUE(
		Remarks[Remarks],
		Remarks[DateTime],
		MostRecentRemarkDateTime,
		Remarks[ID],
		SELECTEDVALUE(Remarks[ID])
	)

[ggzmorsh]

Strange my post got duplicated. I will link the correct post.
Calculate MAX text value - Microsoft Power BI Community

I have tried the formula you gave me and to let you know it does not work as it only returns an error A table of multiple values was supplied where a single value was expected.. I forgot to mention that Table1 where it contains all the entries with duplicates i have created a dax table Table2 which contains distinct of ID from Table1 and from there to get the current remark.

[v-kelly-msft · Accepted solution]

Hi @ggzmorsh ,

You could first create an index column,then create a measure as below:

Measure = 
var _maxdate=CALCULATE(MAX('Table'[Date]),ALL('Table'))
VAR _index=CALCULATE(MAX('Table'[Index]),FILTER(ALL('Table'),'Table'[Date]=_maxdate))
Return
CALCULATE(MAX('Table'[Remarks]),FILTER('Table','Table'[Index]=_index))

Or if you need a calculated column,you could use below dax expression:

Column = 
var _maxdate=CALCULATE(MAX('Table'[Date]),ALL('Table'))
var _index=CALCULATE(MAX('Table'[Index]),FILTER('Table','Table'[Date]=_maxdate))
Return
CALCULATE(MAX('Table'[Remarks]),FILTER('Table','Table'[Index]=_index))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[littlemojopuppy]

That is weird!  I looked at the solutions in the other post...I'd go with one of those.