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 t...
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!

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.

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    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!