Forum Discussion

ggzmorsh's avatar
ggzmorsh
Icon for Helper II rankHelper II
5 years ago
Solved

Calculate MAX text value

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

ggzmorsh , Try measure like

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

 

or


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

ggzmorsh's avatar
ggzmorsh
Icon for Helper II rankHelper II
5 years ago

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.