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

 

 

 

IDDateRemarks
ABC12301/06/2021 12:31:18B
ABC12301/07/2021 12:31:18C
ABC12301/08/2021 12:31:19D
ABC12301/09/2021 12:31:20E
ABC12301/10/2021 12:31:18A
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

7 Replies

  • Gabriel_Walkman's avatar
    Gabriel_Walkman
    Icon for Continued Contributor rankContinued Contributor
    5 years ago

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

     

     

     

  • 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

      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.

    • 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.
       
  • Anonymous's avatar
    Anonymous
    Not applicable
    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

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

      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.