Finding min and max time by date and person

[HxH]

Hi guys, 

I have a table which has these columns: 

Actions (the action done by the operator, can be a list of things)

Operator key (the identifier of the operator) 

Date (the date the action was done) 

Time (the time the action was done) 

The table is basically recording each working day for each operator. What I want to do is find out the first and last action taken by each operator each day (so the min and max time) and label them as "start of day" and "end of day". Basically i need to say something like: for each day, and each operator, the row with the MIN time must be "X", the row with the max time must be "Y", and all the rows in between must be "Z". I think I should use something like groupby with variables to identify the current row but I'm stuck. 

Thanks in advance for the help 

[TomMartens · Accepted solution]

Hey @HxH 

based on this sample data:

I used this DAX statement to create a date column that contains just the date part of the when column:

just the date = DATE(YEAR('Table1'[when]) , MONTH('Table1'[when]) , DAY('Table1'[when]))

Then I used this DAX statement to find the startDateTime (from column when):

startdate = 
var op = 'Table1'[operaor]
var theDay = 'Table1'[just the date]
return
CALCULATE(
    MIN('Table1'[when])
    , FILTER(all(Table1)
    , 'Table1'[operaor] = op && 'Table1'[just the date] = theDay
    )
)

and this to find the enddate:

enddate = 
var op = 'Table1'[operaor]
var theDay = 'Table1'[just the date]
return
CALCULATE(
    MAX('Table1'[when])
    , FILTER(all(Table1)
    , 'Table1'[operaor] = op && 'Table1'[just the date] = theDay
    )
)

After this, the final table looks like this:

Hopefully this provides what you are looking for.

Regards,

Tom

[HxH]

I think I solved it with the following measure 

var currentrow = 'Table'[Time]

return

IF(currentrow=CALCULATE(MINX('Table','Table'[Time]),ALLEXCEPT('Table','Table'[Operator key],'Table'[Date])),"X",IF(currentrow=CALCULATE(MAXX('Table','Table'[Time]),ALLEXCEPT('Table','Table'[Operator key],'Table'[Date])),"Y","Z"))

Seems to be working but if you can spot any errors in the code or reasons why it could be wrong please notify me