Forum Discussion

ggzmorsh's avatar
ggzmorsh
Helper II
5 years ago
Solved

Calculate Max to Return Text

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). 

 

 

 

IDDateRemarks
123ABC01/01/2020 09:22:00D
123ABC01/01/2020 09:22:23C
123ABC01/01/2020 10:15:47A
123ABC01/01/2020 10:15:47B
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

4 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion
    5 years ago

    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's avatar
      ggzmorsh
      Helper II
      5 years ago

      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.

      • littlemojopuppy's avatar
        littlemojopuppy
        Community Champion
        5 years ago

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