cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Potato_Ghost Frequent Visitor
Frequent Visitor

Disregarding Rows With Consecutive Data

Hello Everyone! 

  I am hoping someone can give me a good answer on this. I am trying to do some reporting on our Employee In Times and There are plenty of scenariors where people do not use the FingerPrint Clock Out system correctly. This throws off our clock system.  Is there any way to account for consecutive IN scans and consecutive OUT scans? Ideally, in the case where there are consecutive INs or OUTs, I would like to keep the most rescent IN or OUT Time and disregard the others with the conditions it was the same user, and it was the same day.  I have tried plenty of different methods without much success.  Any suggestions would be greatly appreciated! 

Capture.PNG

8 REPLIES 8
WolfBiber Member
Member

Re: Disregarding Rows With Consecutive Data

Hi,

can you give us some example Data as csv or table data?

Potato_Ghost Frequent Visitor
Frequent Visitor

Re: Disregarding Rows With Consecutive Data

Hi WolfBiber! 

   Thank you very much for your response.  Please find attacehd a CSV of what I have to work with it. 

Potato_Ghost Frequent Visitor
Frequent Visitor

Re: Disregarding Rows With Consecutive Data

Super User
Super User

Re: Disregarding Rows With Consecutive Data

Hi,

 

Based on the data that you have shared, please show the exact result which you are expecting.

Potato_Ghost Frequent Visitor
Frequent Visitor

Re: Disregarding Rows With Consecutive Data

Hi Ashish! 

   I apologize for not being clear.  I am having trouble explaining exactly what I want to happen.  The picture belows depicts a sample of the dataset I have.  I went ahead and pulled out all the time entries I have for one user on one day and sorted by earliest time to latest time.  You can see that he clocked in and out of the building then clocked out twice in a row.  He must have scanned his finger to record the out entry, then forgot something, did not leave the building, then grabbed what he wanted then scanned out of the building again so that the door would open for him, and left. This causes my total hours to sum incorrectly. A IN time entry should be followed by an OUT entry, and on the flip side an OUT entry should be followed by an IN entry. Is there a way to make a measure or Calculated column that says IF a unique user has the same IN/OUT value as the previous record, then the earlier entry that is the same has a value of 0 in the total hour column, else just total hours value? I know that is confusing but I hope that summed it up ok.  Thank you so very much for taking a look.  Capture.PNG

 

Super User
Super User

Re: Disregarding Rows With Consecutive Data

Hi,

 

Can we use this log - If the Date, User ID and Door combination are the same as the previous row, then mark a 1 in that row?  Please let me know.

Potato_Ghost Frequent Visitor
Frequent Visitor

Re: Disregarding Rows With Consecutive Data

Yes! That sounds great! The door column should not be considered though since employees can enter and exit the same door. 

We would want to say If Date, UserID, and IN/OUT are the same as the previous row then mark a 1 in that row. 

WolfBiber Member
Member

Re: Disregarding Rows With Consecutive Data

Hi, 

there is a simple and a more compley way to archive that.

If you just want to know if everybody loggedoff, you can make a compare (measure): 

CountINs = CALCULATE(COUNT(AttendanceSampleData[IN/OUT]);AttendanceSampleData[IN/OUT]="IN")
CountOUTs = CALCULATE(COUNT(AttendanceSampleData[IN/OUT]);AttendanceSampleData[IN/OUT]="OUT")
off = if(IF(ISBLANK([CountOUTs]);0;mod([CountINs];[CountOUTs]))=0;0;1)

Or as Calc Column something like

UserLoggedOut = CALCULATE(MAXX(AttendanceSampleData;AttendanceSampleData[IN/OUT]);AttendanceSampleData[Time]< EARLIER(AttendanceSampleData[Time]);AttendanceSampleData[userid]=EARLIER(AttendanceSampleData[userid]))

be aware: very CPU/Mem consuming.

Sorry I didnt had time to test it.

Please tell us your goal: Analysis or cleansing?

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

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: 135 members 1,977 guests
Please welcome our newest community members: