Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
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].
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!!
// 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!
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |