Forum Discussion
Latest User
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]))))
- 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))
9 Replies
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)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.
- 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))
- 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))
Please provide sample data in usable format (not as a picture) , show the expected outcome, and give sufficient context.
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:
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 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 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.
