Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.