Filtering for Max Date by Customer, then Retrieving Task/Subject (Field Name) by Customer/Date

[samdep]

Hey Everyone!

I have three fields in my table -- Activity Date, Task, Client ID. I created a conditional column to pull in the last date of activity associated with each client - expression below:

LAST ACTIVITY DATE =

VAR CLIENT_ID = Task[Case.ClientID]

RETURN

CALCULATE(MAX(Task[ActivityDate]),

FILTER(Task,

Task[Case.ClientID] = CLIENT_ID))

This works perfectly, retrieving the last activity date associated with each client_id... but, how do I expand on this to pull in the last task/subject tied to both that client_id and MAX(Task[ActivityDate]). The below is giving me one task/subject, so I am assuming it's not looking by both client_id and max[activitydate]... just by max[activitydate].

LAST TASK =

VAR MAXDATE =

CALCULATE(

MAX(Task[ActivityDate]),

FILTER(Task, Task[Case.ClientID]))

RETURN

LOOKUPVALUE(Task[Subject],

Task[ActivityDate], MAXDATE,

Task[Case.ClientID]

)

Sample Data:

Client ID    Task          Date

1234          Initial        1/12/21

1234          Connect    4/20/21

Goal Output in Table:

Client ID   Task           Date

1234         Connect     4/20/21

*Capturing the ClientID, the latest activity date associated with the client & the task/subject associated with both.

Thank you in advance!! 

[Anonymous]

// The calculation in the column you're doing
// is sub-optimal since it uses CALCULATE in each
// and every row; this should be avoided for memory
// and speed reasons. Please use the code the below.

[Last Activity Date] = // calculated column
var vClientID = Task[Case.ClientID]
var Result =
    MAXX(
        Filter(
            Task,
            Task[Case.ClientID] = vClientID
        ),
        Task[ActivityDate]
    )
RETURN 
    Result
    
    
[Last Subject] = // calculated column
var vClientID = Task[Case.ClientID]
var vLastActivityDate = Task[Last Activity Date]
var Result =
    MAXX(
        FILTER(
            Task,
            Task[Case.ClientID] = vClientID
            &&
            Task[Last Activity Date] = vLastActivityDate
        ),
        Task[Subject]
    )
RETURN
    Result

[samdep]

@Anonymous - Thank you so much for the advice on CALCULATE - didn't realize.

Last Activity Date works perfectly - It is pulling in the Latest Date Stamp for Client Activity.

The Subject is not quite working though -- In one scenario I looked at, the last date of activity is 3/31/21 - Outreach and the Task Subject is pulling in Session 5 (which occurred on 2/17/21) -- and the only reason I can think that this might be happening is because there were two subjects that took place on 2/17/21 ("Session 5" and "Resourcing 5") -- and the expression is returning one of the subjects from 2/17/21 instead of the latest on 3/31/21.

Any thoughts? Thanks!