Forum Discussion

Birdjo's avatar
Birdjo
Resolver II
9 years ago
Solved

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:

 

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!

Need Help
  • Sean's avatar
    Sean
    9 years ago

    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! :smileyhappy:

     

    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! :smileyhappy:

8 Replies

  • PavelR's avatar
    PavelR
    Solution Specialist
    9 years ago

    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's avatar
      Birdjo
      Resolver II
      9 years ago

      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's avatar
        PavelR
        Solution Specialist
        9 years ago

        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