Hy guys, I have a table which records every time an employee enters and exits the office via badge identification. I want to calculate the time spent working for each employee, and I want to do it by subtracting the "time outside of the office" (the difference between a badge-in and the previous badge-out) from the total time worked each day (which is the difference between the MIN badge-in and the MAX badge-out of the day), The reason I want to do it this way is that many times employees forget to badge-out of the office and then do another badge-in when they come back, therefore I get multiple consecutive badge-in that I don't want to consider.
The columns of the table are: -User ID -Date -Time (time of badge-in or badge-out) -badge-in/badge-out
DATA EXAMPLE: UserID Date Time Badge A123 12/8/19 8.00 IN A123 12/8/19 9.30 OUT A123 12/8/19 10.00 IN A123 12/8/19 10.30 IN A123 12/8/19 11.00 IN A123 12/8/19 12.00 OUT
For this day and this employee the time outside of office is 30minutes and therefore the total time worked (which will be a measure I think) will be 3.30 hours.
I've been trying to do this for a couple days and I'm stuck, any help would be appreciated Thank
I think I did something very similar (if not the same) in one of my trials and I keep getting a message error saying that I don't have enough memory to create the calculated column with the outtime, which is stupid since it is only a 100k rows table, not some billions rows table. I hoped in a different solution to work around the problem but it seems I will have to solve this memory problem because this is the only way to perform the task I guess
For the out of memory error, Calculated column (especially in Fact Table) is going to impact performance of model.For optimized data analysis, you should denormalize your data. Meaning transformation from schema that's optimized for data entry to schema that's optimized for reporting. Perhaps transforming data in query stage may help in performance.
Also, if you connect the data in import model, please disable "Allow data preview to download in the background" from options / options and settings/data load.
Community Support Team _ Dina Ye If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.