Show only 1 record per employer, per day with earliest time

[DanielB_NL]

Hi all! I'm rather new to Power Bi, I have a question and can't find a topic similar enough to my question, so here it is:

• I have a large table from a time registration. Each day an employee checks in and out multiple times
• I want to see per employ, per day. what the earliest 'time_from_actual' is
• My datasource is a MS SQL database to which I connect via DirectQuery, so I'm limited in my DAX-statements, I think...

Does someone know the answer? Thanks in advance,

[timg · Accepted solution]

Hi Daniel,

If you are visualizing the data within a context that includes the date and employee (e.g. the table below), you could also implicitly set the time_from_actual column to "earliest" by clicking the arrow next to the time_from_actual column in the columns well.

Regards,

Tim

[DanielB_NL]

@timg Hi Tim, you solution worked fine. I have an overview of the starting time per employee per day.

I want to see if people are starting too late, this means after 08:000h. so in in addition to my earlier question: is it also possible to create a filter so that I only show the rows (with the 'earliest' value per day) that are after 08:00h?  The standard filter aren't of any use, because they ask me to specify a date-time combination? 

[rohit_singh]

Hi @DanielB_NL ,

Please try the following :

1) Create a calculated column named "earliest_time_from_actual" on your table which calculates the earliest time for each employee per day.

earliest_time_from_actual =

CALCULATE(

MIN(EmployeeClock[time_from_actual]),

FILTER(

ALLEXCEPT(EmployeeClock, EmployeeClock[employee_code]),

NOT(ISBLANK(EmployeeClock[time_from_actual])) &&

EmployeeClock[project_hour_date] = EARLIER(EmployeeClock[project_hour_date]) &&

EmployeeClock[time_from_actual] < EARLIER(EmployeeClock[time_from_actual])

)

)

2) Create a new table "employee_clock_summarized" that summarizes employee_code, project_hour_date and earliest_time_from_actual

employee_clock_summarized =

SUMMARIZE(

FILTER(EmployeeClock, not(isblank(EmployeeClock[earliest_time_from_actual]))),

EmployeeClock[employee_code],

EmployeeClock[project_hour_date],

EmployeeClock[earliest_time_from_actual]

)

This should give youthe intended result.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂