cancel
Showing results for
Did you mean:
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:

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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."
)
)```

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!

8 REPLIES 8
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

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

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

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

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!

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!

Member

## Re: Count only one entry for a day

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

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.

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

Announcements

#### Win Power BI Swag with Community Kudopalooza!

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

#### Power Platform Summit North America

Register by September 5 to save \$200

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 165 members 1,845 guests
Recent signins: