3 variable expression

[CorpIT]

I have a table of Times (see attached) and a table of Employees (just a column of employee names) for a time tracking app. I’m trying to create a virtual column of “Current Status” to determine if an Employee is clocked in or clocked out. I need the Clock Type of the latest Timestamp for each Employee. I’m not really sure if I need the virtual column on the Employees table or the Times table? I also need help with the formula. This is as close as I’ve gotten, but it only works for one employee at a time:
SELECT(Times[Clock Type],
AND([Timestamp]=MAX(Times[Timestamp]), [Employee]=[_THISROW])
)

[LeventK]

@CorpIT
Your expression needs a slight edit:

SELECT(
	Times[Clock Type],
	AND(
		[Timestamp]=MAX(Times[Timestamp]),
		[Employee]=[_THISROW].[Employee]
	)
)

[CorpIT]

@LeventK
Still not quite working. It’s only showing the last Employee I clocked in, not all the employees at the same time.

[LeventK]

@CorpIT
You are returning a List not a single value which I assume you are using in the second row of your Deck View. Try with this and set the type of the VC as Text

ANY(
	SELECT(
		Times[Clock Type],
		AND(
			[Timestamp]=MAX(Times[Timestamp]),
			[Employee]=[_THISROW].[Employee]
		)
	)
)

[CorpIT]

I updated the formula and changed the VC type to Text. Still only getting one employee at a time…

[LeventK · Accepted solution]

@CorpIT
You already have a REF_ROWS column for each of your employee. I just noticed that when I open the post from my laptop. So I believe below AppFormula will be more appropriate:

ANY(
	SELECT(
		[Related Times][Clock Type],[Timestamp]=MAX(SELECT(Times[Timestamp],[Employee]=[_THISROW].[Employee])),
	)
)