Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
samdep
Advocate II
Advocate II

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

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

2 REPLIES 2
Anonymous
Not applicable

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

@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! 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.