Request user measure

[PowerBI123456]

Hi, need some help with a measure. I am dealing with a large model and millions of rows, so prefer to do this in a measure. I am trying to figure out who the last requester is based on a response per account. For example in the example below, the requester for account 1 is Amy and for account 2, the requester is Jerry. Thanks in advance!!

[nvprasad]

Hi,

Can you please try with the below dax measure.

Last_Requester =

VAR abc =

CALCULATE (

MAX ( 'Sample'[Index] ),

'Sample'[Action] = "Request",

ALLEXCEPT ( 'Sample', 'Sample'[Action], 'Sample'[Date] )

)

VAR lastuser =

LOOKUPVALUE ( 'Sample'[User], 'Sample'[Index], abc )

RETURN

lastuser

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad

[amitchandak · Accepted solution]

@PowerBI123456 , Try a measure like

Last Date = calculate(max(Table[user]), filter(Table, Table[Action] ="request" && Table[date] = calculate(max(Table[Date]),Table[Action] ="request", allexcept(Table, Table[Account]))) )

[PowerBI123456]

@amitchandak  Thanks! Anyway to incorporate the response piece? I forgot to mention, I only want to see the last request if there was a response. For example, in account 2 below, I dont want to call out the request by Tom since there was no response, only Jerry's since that was the last request before the response. Does that make sense? 

[v-kelly-msft]

Hi  @PowerBI123456 ,

Create a measure as below:

Last Requester = 
var _responseindex=CALCULATE(MAX('Table'[Index]),FILTER(ALL('Table'),'Table'[Account]=MAX('Table'[Account])&&'Table'[Action]="Response"))
var _requestindex=CALCULATE(MAX('Table'[Index]),FILTER(ALL('Table'),'Table'[Account]=MAX('Table'[Account])&&'Table'[Index]<_responseindex&&'Table'[Action]="Request"))
Return
CALCULATE(MAX('Table'[User]),FILTER('Table','Table'[Index]=_requestindex))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!