cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Birdjo Member
Member

Count only one entry for a day

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:

ef0fadafcc

ef0fb62497

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Sean Super Contributor
Super Contributor

Re: Count only one entry for a day

@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."
    )
)

Count only 1 entry per day.png

Hope this helps.

Good Luck! Smiley Happy

 

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...

 

Count only 1 entry per day - 2.png

This should hopefully give you some ideas.

Good Luck! Smiley Happy

8 REPLIES 8
PavelR Established Member
Established Member

Re: Count only one entry for a day

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 Member
Member

Re: Count only one entry for a day

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 Established Member
Established Member

Re: Count only one entry for a day

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 Member
Member

Re: Count only one entry for a day

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.

Highlighted
Sean Super Contributor
Super Contributor

Re: Count only one entry for a day

@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."
    )
)

Count only 1 entry per day.png

Hope this helps.

Good Luck! Smiley Happy

 

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...

 

Count only 1 entry per day - 2.png

This should hopefully give you some ideas.

Good Luck! Smiley Happy

Birdjo Member
Member

Re: Count only one entry for a day

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!

Birdjo Member
Member

Re: Count only one entry for a day

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

MattScruggs Frequent Visitor
Frequent Visitor

Re: Count only one entry for a day

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. 

 Example1.png

 

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

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 165 members 1,845 guests
Please welcome our newest community members: