Identifying last activity in a process and outputting value from different column

[LarryFisherman]

Hi all,

I am working on some processflow data, where I need to check who the last user is for approval within two approval types. Below table showcases how my data looks like, with the last two columns (in bold) being the result I am looking for.

DateTime	ID	Type	Action	User	Last Approval 1 user	Last Approval 2 user
01.01.2022 10:00	1	Approval 1	Approval	AA	DD	EE
01.01.2022 12:00	1	Approval 2	Approval	BB	DD	EE
01.01.2022 12:30	1	Edit	Edit	CC	DD	EE
01.01.2022 14:00	1	Approval 1	Approval	DD	DD	EE
01.01.2022 15:00	1	Approval 2	Approval	EE	DD	EE

I am looking for solutions in either DAX or Power query.

Thanks beforehand.

[LarryFisherman · Accepted solution]

Hi,

I managed solve this question eventually. Posting it here, if anyone has the same issue:

I create a calculated column that identifies that last entry for Approval 1, I also make a second calculated column where I replace Approval 1 (Marked with bold) with approval 2, to get one column for each approval.

Last Approval 1 user =
VAR MaxDateTime = CALCULATE(MAX(Table1[DateTime]),
FILTER(Table1, Table1[ID] = EARLIER(Table1[ID]) && Table1[Type] = "Approval 1"))
RETURN
CALCULATE(
MAX(Table1[User]),
FILTER(Table1, Table1[ID] = EARLIER(Table1[ID]) && Table1[DateTime] = MaxDateTime)
)

This could potentially also have been solved by incorporating RANK, as @christinepayton mentioned, in above DAX, but it wasn't neccesary in this case.

[christinepayton]

I think it should work to use RANK() to order them (assuming on datetime), if you ranked them descending instead of ascending and took the #1 rank that would be the last item. If you're trying to rank over some particular field as the differentiator, use RANKX() instead.