Get string value of column based on maximum date time and minimum date time

[Anonymous]

I have data like that its an attendance scedual

Mode	Date	Datetime	username
f1	8/4/2017	8/4/17 7:15 AM	A
f2	8/4/2017	8/4/17 12:00 PM	A
f2	8/4/2017	8/4/17 8:00 PM	A
f3	8/5/2017	8/5/17 7:15 AM	A
f3	8/5/2017	8/5/17 12:00 PM	A
f4	8/6/2017	8/6/17 7:00 AM	B
f5	8/6/2017	8/6/17 10:00 PM	B

and I want a report like that

A
8/4/2017
username	Start Mode	EndMode	Start Time	End Time	Work time
A	f1	f2	8/4/17 7:15 AM	8/4/17 8:00 PM	12,45 hours

I did the Start Time Equation, End Time Equations and Work time

Start Time :=FIRSTNONBLANK(DIM_END_ACTIVITY_TIME[FULL_DATE],FIRSTNONBLANK('FACT_EMPLOYEE'[EndActivityTime],1))

End Time:=LASTNONBLANK('DIM_END_ACTIVITY_TIME'[FULL_DATE],FIRSTNONBLANK('FACT_EMPLOYEE'[EndActivityTime],1))

work time :=End Time-Start Time

but I couldn't do StartMode and EndMode how to make them?

[v-chuncz-msft · Accepted solution]

@Anonymous,

Given the Start Time and End Time, you may use LOOKUPVALUE Function to search the StartMode and EndMode.