cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Potato_Ghost
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
Microsoft
Microsoft

Hi,

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

Hi WolfBiber! 

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

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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. 

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors