Count only one entry for a day

[Birdjo]

Hello,

I am working on a report which calculates and determines the shifts of our employees by their clock in and clock out. 

The data I had was limited to employees' in/out time and date.
I had managed to use some functions to determine things like their shifts, if they were late, if they exit work earlier, if the exit later than their work time.

Counting the shifts I try to calculate the number of shift for a period or total shifts as days worked.
But there's a problem which makes nearly all my efforts and report deceiveful.

Some people frequently clock out twice and that makes them have 2 shifts for a day. 

Here are some examples of the dataset:

Here's the DAX formula I use to calculate the number of shifts.

Number of regular shifts =
 CALCULATE(COUNTA(EventRecordOUT[Shift]);
    OR(EventRecordOUT[Shift] = "Regular -15 min.";
    OR(EventRecordOUT[Shift] = "Regular";
    OR(EventRecordOUT[Shift] = "Regular +30 min.";
    EventRecordOUT[Shift] = "Regular +60 min."))))

So would you please help me by telling me or giving me idea how to make it that the formula counts only 1 shift per day.

Thanks in advance!

[PavelR]

Hi @Birdjo,

if you don't need these duplicate rows, then just remove them from your dataset. Then you don't have to solve it in your DAX formula.

Regards.

Pavel

[Birdjo]

Hi @PavelR,

Thank you very much for your response. I think it is a clever and simple solution.

But we don't have only 1 employee, we have nearly 300 employees and if I remove the duplicates of the date what will happen is we will have only 1 employee entry or exit. 
Do you know how to remove the duplicates for date for employee. So we can keep the employees but remove only their duplicate dates?

Kind regards,
Birdjo

[PavelR]

Yeah @Birdjo, I exactly get it what you mean.

I have created a sample (only few rows for a test) and I think I know how to solve it.

I would try to create new column which will merge the whole Date column and just hours from Time column. Then select this new column and Employee column and remove duplicates (Home -> Reduce rows -> Remove duplicates)

Please test it on your much bigger dataset and let me know, if it helps.

Regards.

Pavel

[Birdjo]

I already have Date and time column. It is showing down to seconds. So it is impossible to clock in in the same second. Usually they clock in with two seconds difference.

Many employees enter in the same minute. If that wasn't the case we could have changed the date and time format to dd/mm/yyy hh/mm (without ss) and remove the duplicates. But many employees enter in the same minute.

[Sean · Accepted solution]

@Birdjo

Number of regular shifts ALT =
CALCULATE (
    DISTINCTCOUNT ( EventRecordOUT[Date] );
    FILTER (
        EventRecordOUT;
        EventRecordOUT[Shift] = "Regular -15 min."
            || EventRecordOUT[Shift] = "Regular"
            || EventRecordOUT[Shift] = "Regular +30 min."
            || EventRecordOUT[Shift] = "Regular +60 min."
    )
)

Hope this helps.

Good Luck!

EDIT: Depending on how you actually display this you may have to change the Measure a bit to get the correct Total

So I created a Test Column

Test Column = [Employee]&" - "&[Date]

And then Modified the above Measure like this...

Number of Regular Shifts NEW =
IF (
    HASONEVALUE ( EventRecordOUT[Date] );
    CALCULATE (
        DISTINCTCOUNT ( 'EventRecordOUT'[Date] );
        FILTER (
            'EventRecordOUT';
            'EventRecordOUT'[Shift] = "Regular -15 min."
                || 'EventRecordOUT'[Shift] = "Regular"
                || EventRecordOUT[Shift] = "Regular +30 min."
                || EventRecordOUT[Shift] = "Regular +60 min."
        )
    );
    CALCULATE (
        DISTINCTCOUNT ( EventRecordOUT[Test Column] );
        FILTER (
            'EventRecordOUT';
            'EventRecordOUT'[Shift] = "Regular -15 min."
                || 'EventRecordOUT'[Shift] = "Regular"
                || EventRecordOUT[Shift] = "Regular +30 min."
                || EventRecordOUT[Shift] = "Regular +60 min."
        )
    )
)

As I said the condition plus the calculation may have to be changed depending on how you visualize it...

This should hopefully give you some ideas.

Good Luck!

[MattScruggs]

I am having a similar issue. 

Im trying to return a 1 per employee per day, but a 0 if their name appears multiple times within the same day.

Attached is an example of how I would like it to appear within PowerBI. 

 

Im so stuck, I dont even know where to begin. 

[Birdjo]

This calculated column can give me even more analytical opportunities. Thank you very much!

[Birdjo]

Thank you very much @Sean That solved the problem. I didn't have the time to test it for all of the shifts but I will apply it tomorrow for all of them.

Thanks again!