Forum Discussion

ggzmorsh's avatar
ggzmorsh
Helper 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

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