Latest User

[PowerBI123456]

I am using the measure below to determine who put a request to a correspodning response. The problem is that it shows all the users who put in a request before the response date. I just want the latest one. Any tips

Requester = calculate(max('FACT: Activity'[UserID]), Filter('FACT: Activity', 'FACT: Activity' [ActionID] = “request” && 'FACT: Activity' [Activity Date] <= calculate(max('FACT: Activity' [Activity Date]), 'FACT: Activity' [ActionID] = “response” , ALLEXCEPT('FACT: Activity', 'FACT: Activity' [Account]))))

[lbendlin · Accepted solution]

Last Request User =

var resp=CALCULATE(max(Activities[Index]),ALLEXCEPT(Activities,Activities[Account]),Activities[Activity ID]="Response")

var req=CALCULATE(max(Activities[Index]),ALLEXCEPT(Activities,Activities[Account]),Activities[Activity ID]="Request",Activities[Index]<resp)

return if(resp=BLANK(),resp,CALCULATE(max(Activities[User ID]),ALLEXCEPT(Activities,Activities[Account]),Activities[Index]=req))

[PowerBI123456]

@lbendlin Thanks a lot!!  Sorry to add one other criteria. I didnt realize it until I tested it and I am dealing with 10+ million rows, but another request can happen after a response. The measure is calculating the max request even if its after the response. For example with account 3, there is a request (Angela's) after the response but I don't care for that request since there is no response after. I only care about Jenny's request for account 3 . Hope that makes sense. Thank you so much for all your help!! I keep trying to add a table but its not letting me. 

[lbendlin]

Last Request User =

var resp=CALCULATE(max(Activities[Index]),ALLEXCEPT(Activities,Activities[Account]),Activities[Activity ID]="Response")

var req=CALCULATE(max(Activities[Index]),ALLEXCEPT(Activities,Activities[Account]),Activities[Activity ID]="Request")

return if(resp=BLANK(),resp,CALCULATE(max(Activities[User ID]),ALLEXCEPT(Activities,Activities[Account]),Activities[Index]=req))

[PowerBI123456]

@lbendlin  Sorry its not letting me attached a table. I keep getting an error message. But I added an account 4 which shows no response yet. I wouldnt want to know who the requester is for that account. 

[lbendlin]

You did not specify that clear enough (at least not for me), and your sample data has responses for all cases. Please provide better sample data, this time in a table, please.

[lbendlin]

You attach data by posting the tabular data here.

here's your measure:

Last Request User :=

var req=CALCULATE(max(Activities[Index]),ALLEXCEPT(Activities,Activities[Account]),Activities[Activity ID]="Request")

return CALCULATE(max(Activities[User ID]),ALLEXCEPT(Activities,Activities[Account]),Activities[Index]=req)

[PowerBI123456]

@lbendlin Thanks a lot , but the measure doesnt take into account a response. I only want the last request identified if there was a response. 

[lbendlin]

Please provide sample data in usable format (not as a picture) , show the expected outcome, and give sufficient context.

[PowerBI123456]

How do I attach data? Here is what it looks like:

Here is what my measure getting me:

Here is what I want. I only want to see the last requester if there was a response: