Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I'm very new to this.. I tried a couple of things...
Hello @rf27
What should the expected output be?
These are time, and having total of times doesn't seem to be of any sense to me.
To me this seems more a topic for power query rather then DAX
All the best
Jimmy
Hi Jimmy,
Thank you for your reply.
What I'm looking for is two things,
Total hours worked per date
Then Total hours per days worked .
Ex: day one- 6 hrs, day 2- 8hrs, day 3- 10hrs, and day 4- 8hrs
Total 32 hours
Hello @rf27
how the start and how the end time is defined? A was not able to identify it. What I did already mention is that in one day i saw 3 times. So how to identify what is the start and what is the end date?
What would also be important to now, is what exactly is you expected output. I can think of transforming date (poviting start and end) and with this a easy measure in DAX could be created.
Jimmy
Hey Jimmy,
The first transaction , and the last transaction of the day counts for the time duration... I cut out the important piece.. I have an 1 and 0 the represents my first in and last transaction. 1 = IN 0= OUT I'm looking to tabulate the IN & OUT of every day... I hope this brings clarity to my questions..
Hello @rf27
are they connected in any way? meaning.. how to belong the first 1 with the first 0?
Possible logics to create your solution could be like this:
- split always every second row
- use the date (if the table exactly contains 2 rows per otherwise - otherwise logic would fail)
Another remark.. so you are doing already a trasforming of your data, keeping only first and last time of a day?
Could you please post a real table, that I can use it to build your solution
Jimmy
Hey Jimmy,
Yes, I have two unique identifiers.
empid, cardnum this connects me to the 1&0 transactions per person.. I also have a filter to get the 1&0 based on the reader desc.
Let me clean up the data set it's rather large
Richard
events.event_time_utc event.evdescr reader.readerdesc empid events.cardnum buenos_aires DATE TIME badgetyp.r_name in_out
12/3/19 8:21 PM Access Granted 1443ASM POST 2 TS4 EXIT C03-P3-R1 24808 20600609 12/3/19 5:21 PM 12/03/19 17:21:42 Contractor 0
12/3/19 10:38 PM Access Granted 1493 ROMULUS T/S 7 EAST OUT -C23-P2-R2 26792 20302725 12/3/19 7:38 PM 12/03/19 19:38:52 Contractor 0
12/3/19 1:07 PM Access Granted 1493 ROMULUS T/S 6 EAST IN -C23-P1-R7 26792 20302725 12/3/19 10:07 AM 12/03/19 10:07:08 Contractor 1
12/3/19 8:28 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 27882 20632002 12/3/19 5:28 PM 12/03/19 17:28:14 Contractor 0
12/3/19 12:28 PM Access Granted 1443ASM POST 2 TS3 EXIT C03-P1-R1 27983 20632009 12/3/19 9:28 AM 12/03/19 9:28:23 Contractor 0
12/3/19 12:28 PM Access Granted 1443ASM POST 2 TS4 EXIT C03-P3-R1 28038 315970 12/3/19 9:28 AM 12/03/19 9:28:20 Contractor 0
12/3/19 8:29 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 28064 314473 12/3/19 5:29 PM 12/03/19 17:29:19 Contractor 0
12/3/19 8:33 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 28130 520737 12/3/19 5:33 PM 12/03/19 17:33:20 Contractor 0
12/3/19 7:29 PM Access Granted 1493 ROMULUS T/S 6 EAST OUT -C23-P1-R8 28188 20302713 12/3/19 4:29 PM 12/03/19 16:29:36 Contractor 0
12/3/19 10:51 AM Access Granted 1493 ROMULUS T/S 6 EAST IN -C23-P1-R7 28188 20302713 12/3/19 7:51 AM 12/03/19 7:51:46 Contractor 1
12/3/19 11:56 AM Access Granted 1493 ROMULUS T/S SECURITY LOBBY OUT -C22-P2-R2 28188 20302713 12/3/19 8:56 AM 12/03/19 8:56:41 Contractor 0
12/3/19 2:30 PM Access Granted 1493 ROMULUS T/S SECURITY LOBBY OUT -C22-P2-R2 28188 20302713 12/3/19 11:30 AM 12/03/19 11:30:54 Contractor 0
12/3/19 2:31 PM Access Granted 1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1 28188 20302713 12/3/19 11:31 AM 12/03/19 11:31:34 Contractor 1
12/3/19 11:57 AM Access Granted 1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1 28188 20302713 12/3/19 8:57 AM 12/03/19 8:57:12 Contractor 1
12/3/19 2:06 PM Access Granted 1443ASM POST 2 TS6 EXIT C03-P2-R2 39905 20500896 12/3/19 11:06 AM 12/03/19 11:06:45 Contractor 0
12/3/19 9:07 PM Access Granted 1443ASM POST 2 TS6 EXIT C03-P2-R2 39905 20500896 12/3/19 6:07 PM 12/03/19 18:07:22 Contractor 0
12/3/19 10:04 PM Access Granted 1443ASM POST 1 TS3 EXIT C11-P3-R2 40386 20322132 12/3/19 7:04 PM 12/03/19 19:04:22 Unknown 0
12/3/19 6:39 PM Access Granted 5014 ENT CSS CORREDIZA 3 C14-P6-R1 41691 301497 12/3/19 3:39 PM 12/03/19 15:39:40 Contractor 1
12/3/19 8:10 PM Access Granted 5014 ENT CSS TOR 4 RA C12-P3-R2 41691 301497 12/3/19 5:10 PM 12/03/19 17:10:54 Contractor 1
12/3/19 1:17 PM Access Granted 5014 ENT CSS CORREDIZA 2 C14-P7-R1 41691 301497 12/3/19 10:17 AM 12/03/19 10:17:22 Contractor 1
12/3/19 6:38 PM Access Granted 5014 ENT CSS CORREDIZA 2 C14-P7-R1 41691 301497 12/3/19 3:38 PM 12/03/19 15:38:38 Contractor 1
12/3/19 1:16 PM Access Granted 5014 ENT CSS TOR 1 RA C12-P0-R2 41691 301497 12/3/19 10:16 AM 12/03/19 10:16:42 Contractor 1
12/3/19 8:23 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 41804 20619953 12/3/19 5:23 PM 12/03/19 17:23:22 Contractor 0
12/3/19 3:19 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 12:19 PM 12/03/19 12:19:40 Contractor 0
12/3/19 5:47 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 2:47 PM 12/03/19 14:47:27 Contractor 0
12/3/19 6:22 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 3:22 PM 12/03/19 15:22:12 Contractor 0
12/3/19 6:50 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 3:50 PM 12/03/19 15:50:59 Contractor 0
12/3/19 6:51 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 3:51 PM 12/03/19 15:51:13 Contractor 0
12/3/19 2:46 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 11:46 AM 12/03/19 11:46:26 Contractor 0
12/3/19 3:14 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 12:14 PM 12/03/19 12:14:04 Contractor 0
12/3/19 3:25 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 12:25 PM 12/03/19 12:25:06 Contractor 0
12/3/19 3:34 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 12:34 PM 12/03/19 12:34:19 Contractor 0
12/3/19 3:37 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 12:37 PM 12/03/19 12:37:40 Contractor 0
12/3/19 3:45 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 12:45 PM 12/03/19 12:45:31 Contractor 0
12/3/19 3:47 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 12:47 PM 12/03/19 12:47:14 Contractor 0
12/3/19 3:56 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 12:56 PM 12/03/19 12:56:52 Contractor 0
12/3/19 4:00 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 1:00 PM 12/03/19 13:00:00 Contractor 0
12/3/19 4:13 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 1:13 PM 12/03/19 13:13:27 Contractor 0
12/3/19 4:44 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 1:44 PM 12/03/19 13:44:50 Contractor 0
12/3/19 5:42 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 2:42 PM 12/03/19 14:42:50 Contractor 0
12/3/19 6:03 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 3:03 PM 12/03/19 15:03:18 Contractor 0
12/3/19 6:38 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 3:38 PM 12/03/19 15:38:19 Contractor 0
12/3/19 6:50 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/3/19 3:50 PM 12/03/19 15:50:34 Contractor 0
12/3/19 10:53 PM Access Granted 1443ASM POST 2 TS6 EXIT C03-P2-R2 41838 1116381 12/3/19 7:53 PM 12/03/19 19:53:19 Contractor 0
12/3/19 8:18 AM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 42601 320316 12/3/19 5:18 AM 12/03/19 5:18:12 Contractor 0
12/3/19 11:08 AM Access Granted 1443ASM POST 2 TS6 EXIT C03-P2-R2 42601 320316 12/3/19 8:08 AM 12/03/19 8:08:48 Contractor 0
12/3/19 4:07 PM Access Granted 1493 ROMULUS T/S SECURITY LOBBY OUT -C22-P2-R2 44085 410614 12/3/19 1:07 PM 12/03/19 13:07:15 Contractor 0
12/3/19 10:48 AM Access Granted 1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1 44085 410614 12/3/19 7:48 AM 12/03/19 7:48:42 Contractor 1
12/4/19 8:25 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 24808 20600609 12/4/19 5:25 PM 12/04/19 17:25:29 Contractor 0
12/4/19 10:23 PM Access Granted 1493 ROMULUS T/S 7 EAST OUT -C23-P2-R2 26792 20302725 12/4/19 7:23 PM 12/04/19 19:23:54 Contractor 0
12/4/19 1:16 PM Access Granted 1493 ROMULUS T/S 6 EAST IN -C23-P1-R7 26792 20302725 12/4/19 10:16 AM 12/04/19 10:16:18 Contractor 1
12/4/19 8:28 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 27882 20632002 12/4/19 5:28 PM 12/04/19 17:28:04 Contractor 0
12/4/19 4:33 AM Access Granted 1443ASM POST 2 TS4 EXIT C03-P3-R1 27915 20616794 12/4/19 1:33 AM 12/04/19 1:33:38 Contractor 0
12/4/19 12:28 PM Access Granted 1443ASM POST 2 TS4 EXIT C03-P3-R1 27983 20632009 12/4/19 9:28 AM 12/04/19 9:28:52 Contractor 0
12/4/19 12:28 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 28038 315970 12/4/19 9:28 AM 12/04/19 9:28:52 Contractor 0
12/4/19 8:30 PM Access Granted 1443ASM POST 2 TS3 EXIT C03-P1-R1 28064 314473 12/4/19 5:30 PM 12/04/19 17:30:01 Contractor 0
12/4/19 3:02 PM Access Granted 1443ASM POST 2 TS4 EXIT C03-P3-R1 28064 314473 12/4/19 12:02 PM 12/04/19 12:02:32 Contractor 0
12/4/19 8:33 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 28130 520737 12/4/19 5:33 PM 12/04/19 17:33:02 Contractor 0
12/4/19 7:29 PM Access Granted 1493 ROMULUS T/S 6 EAST OUT -C23-P1-R8 28188 20302713 12/4/19 4:29 PM 12/04/19 16:29:46 Contractor 0
12/4/19 4:40 PM Access Granted 1493 ROMULUS T/S 6 EAST OUT -C23-P1-R8 28188 20302713 12/4/19 1:40 PM 12/04/19 13:40:04 Contractor 0
12/4/19 10:55 AM Access Granted 1493 ROMULUS T/S 5 EAST IN -C23-P1-R5 28188 20302713 12/4/19 7:55 AM 12/04/19 7:55:23 Contractor 1
12/4/19 4:52 PM Access Granted 1493 ROMULUS T/S 6 EAST IN -C23-P1-R7 28188 20302713 12/4/19 1:52 PM 12/04/19 13:52:17 Contractor 1
12/4/19 2:08 PM Access Granted 1493 ROMULUS T/S SECURITY LOBBY OUT -C22-P2-R2 28188 20302713 12/4/19 11:08 AM 12/04/19 11:08:34 Contractor 0
12/4/19 2:09 PM Access Granted 1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1 28188 20302713 12/4/19 11:09 AM 12/04/19 11:09:21 Contractor 1
12/4/19 10:02 PM Access Granted 1443ASM POST 1 TS3 EXIT C11-P3-R2 40386 20322132 12/4/19 7:02 PM 12/04/19 19:02:21 Unknown 0
12/4/19 7:29 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 41804 20619953 12/4/19 4:29 PM 12/04/19 16:29:07 Contractor 0
12/4/19 3:00 PM Access Granted 1443ASM TRIM SOUTH TURNSTILE 1 EXIT C21-P0-R1 41838 1116381 12/4/19 12:00 PM 12/04/19 12:00:32 Contractor 0
12/4/19 3:00 PM Access Granted 1443ASM TRIM SOUTH TURNSTILE 2 EXIT C21-P1-R2 41838 1116381 12/4/19 12:00 PM 12/04/19 12:00:35 Contractor 0
12/4/19 3:03 PM Access Granted 1443ASM POST 3 TURNSTILE 2 EXIT C22-P1-R2 41838 1116381 12/4/19 12:03 PM 12/04/19 12:03:22 Contractor 0
12/4/19 3:03 PM Access Granted 1443ASM POST 3 TURNSTILE 3 EXIT C22-P2-R2 41838 1116381 12/4/19 12:03 PM 12/04/19 12:03:19 Contractor 0
12/4/19 3:00 PM Access Granted 1443ASM TRIM SOUTH TURNSTILE 3 EXIT C21-P2-R2 41838 1116381 12/4/19 12:00 PM 12/04/19 12:00:38 Contractor 0
12/4/19 3:03 PM Access Granted 1443ASM POST 3 TURNSTILE 1 EXIT C22-P0-R2 41838 1116381 12/4/19 12:03 PM 12/04/19 12:03:27 Contractor 0
12/4/19 1:28 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/4/19 10:28 AM 12/04/19 10:28:50 Contractor 0
12/4/19 1:24 PM Access Granted 1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1 41838 1116381 12/4/19 10:24 AM 12/04/19 10:24:23 Contractor 0
12/4/19 3:15 PM Access Granted 1443ASM BODY SHOP T/S 2 EXIT C20-P2-R2 41838 1116381 12/4/19 12:15 PM 12/04/19 12:15:32 Contractor 0
12/4/19 3:11 PM Access Granted 1443ASM POST 1 TS2 EXIT C01-P0-R2 41838 1116381 12/4/19 12:11 PM 12/04/19 12:11:40 Contractor 0
12/4/19 3:11 PM Access Granted 1443ASM POST 1 TS1 EXIT C01-P0-R1 41838 1116381 12/4/19 12:11 PM 12/04/19 12:11:41 Contractor 0
12/4/19 3:15 PM Access Granted 1443ASM BODY SHOP T/S 1 EXIT C20-P1-R2 41838 1116381 12/4/19 12:15 PM 12/04/19 12:15:35 Contractor 0
12/4/19 3:30 PM Access Granted 1443ASM PAINT SHOP TS 2 EXIT C18-P2-R1 41838 1116381 12/4/19 12:30 PM 12/04/19 12:30:03 Contractor 0
12/4/19 3:30 PM Access Granted 1443ASM PAINT SHOP TS 1 EXIT C18-P1-R2 41838 1116381 12/4/19 12:30 PM 12/04/19 12:30:01 Contractor 0
12/4/19 3:30 PM Access Granted 1443ASM PAINT SHOP TS 3 EXIT C18-P3-R1 41838 1116381 12/4/19 12:30 PM 12/04/19 12:30:07 Contractor 0
12/4/19 3:11 PM Access Granted 1443ASM POST 1 TS3 EXIT C11-P3-R2 41838 1116381 12/4/19 12:11 PM 12/04/19 12:11:36 Contractor 0
12/4/19 7:00 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 41838 1116381 12/4/19 4:00 PM 12/04/19 16:00:05 Contractor 0
12/4/19 3:11 PM Access Granted 1443ASM POST 1 TS4 EXIT C11-P3-R1 41838 1116381 12/4/19 12:11 PM 12/04/19 12:11:35 Contractor 0
12/4/19 6:38 PM Access Granted 1443ASM POST 2 TS6 EXIT C03-P2-R2 41838 1116381 12/4/19 3:38 PM 12/04/19 15:38:32 Contractor 0
12/4/19 3:03 PM Access Granted 1443ASM POST 3 TURNSTILE 1 ENT C22-P0-R1 41838 1116381 12/4/19 12:03 PM 12/04/19 12:03:05 Contractor 1
12/4/19 3:03 PM Access Granted 1443ASM POST 3 TURNSTILE 3 ENT C22-P2-R1 41838 1116381 12/4/19 12:03 PM 12/04/19 12:03:10 Contractor 1
12/4/19 3:00 PM Access Granted 1443ASM TRIM SOUTH TURNSTILE 3 ENT C21-P2-R1 41838 1116381 12/4/19 12:00 PM 12/04/19 12:00:17 Contractor 1
12/4/19 3:03 PM Access Granted 1443ASM POST 3 TURNSTILE 2 ENT C22-P1-R1 41838 1116381 12/4/19 12:03 PM 12/04/19 12:03:07 Contractor 1
12/4/19 3:00 PM Access Granted 1443ASM TRIM SOUTH TURNSTILE 2 ENT C21-P1-R1 41838 1116381 12/4/19 12:00 PM 12/04/19 12:00:20 Contractor 1
12/4/19 3:00 PM Access Granted 1443ASM TRIM SOUTH TURNSTILE 1 ENT C21-P0-R2 41838 1116381 12/4/19 12:00 PM 12/04/19 12:00:23 Contractor 1
12/4/19 3:00 PM Access Granted 1443ASM TRIM SOUTH ADA ENT C21-P3-R1 41838 1116381 12/4/19 12:00 PM 12/04/19 12:00:26 Contractor 1
12/4/19 11:00 AM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 42601 320316 12/4/19 8:00 AM 12/04/19 8:00:22 Contractor 0
12/4/19 7:31 PM Access Granted 1493 ROMULUS T/S SECURITY LOBBY OUT -C22-P2-R2 44085 410614 12/4/19 4:31 PM 12/04/19 16:31:35 Contractor 0
12/4/19 10:17 AM Access Granted 1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1 44085 410614 12/4/19 7:17 AM 12/04/19 7:17:23 Contractor 1
12/5/19 8:22 PM Access Granted 1443ASM POST 2 TS6 EXIT C03-P2-R2 24808 20600609 12/5/19 5:22 PM 12/05/19 17:22:12 Contractor 0
12/5/19 11:35 PM Access Granted 1493 ROMULUS T/S 6 EAST OUT -C23-P1-R8 26792 20302725 12/5/19 8:35 PM 12/05/19 20:35:47 Contractor 0
12/5/19 1:58 PM Access Granted 1493 ROMULUS T/S 6 EAST IN -C23-P1-R7 26792 20302725 12/5/19 10:58 AM 12/05/19 10:58:03 Contractor 1
12/5/19 8:28 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 27882 20632002 12/5/19 5:28 PM 12/05/19 17:28:05 Contractor 0
12/5/19 4:33 AM Access Granted 1443ASM POST 2 TS4 EXIT C03-P3-R1 27915 20616794 12/5/19 1:33 AM 12/05/19 1:33:38 Contractor 0
12/5/19 12:28 PM Access Granted 1443ASM POST 2 TS4 EXIT C03-P3-R1 27983 20632009 12/5/19 9:28 AM 12/05/19 9:28:23 Contractor 0
12/5/19 12:28 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 28038 315970 12/5/19 9:28 AM 12/05/19 9:28:28 Contractor 0
12/5/19 8:28 PM Access Granted 1443ASM POST 2 TS3 EXIT C03-P1-R1 28064 314473 12/5/19 5:28 PM 12/05/19 17:28:45 Contractor 0
12/5/19 8:33 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 28130 520737 12/5/19 5:33 PM 12/05/19 17:33:33 Contractor 0
12/5/19 10:54 AM Access Granted 1493 ROMULUS T/S 7 EAST IN -C23-P2-R1 28188 20302713 12/5/19 7:54 AM 12/05/19 7:54:49 Contractor 1
12/5/19 7:29 PM Access Granted 1493 ROMULUS T/S 5 EAST OUT -C23-P1-R6 28188 20302713 12/5/19 4:29 PM 12/05/19 16:29:28 Contractor 0
12/5/19 2:14 PM Access Granted 1493 ROMULUS T/S SECURITY LOBBY OUT -C22-P2-R2 28188 20302713 12/5/19 11:14 AM 12/05/19 11:14:30 Contractor 0
12/5/19 2:39 PM Access Granted 1493 ROMULUS T/S SECURITY LOBBY OUT -C22-P2-R2 28188 20302713 12/5/19 11:39 AM 12/05/19 11:39:01 Contractor 0
12/5/19 2:14 PM Access Granted 1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1 28188 20302713 12/5/19 11:14 AM 12/05/19 11:14:59 Contractor 1
12/5/19 2:39 PM Access Granted 1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1 28188 20302713 12/5/19 11:39 AM 12/05/19 11:39:25 Contractor 1
12/5/19 1:09 PM Access Granted 1443ASM POST 2 TS6 EXIT C03-P2-R2 39905 20500896 12/5/19 10:09 AM 12/05/19 10:09:13 Contractor 0
12/5/19 8:53 PM Access Granted 1443ASM POST 2 TS6 EXIT C03-P2-R2 39905 20500896 12/5/19 5:53 PM 12/05/19 17:53:03 Contractor 0
12/5/19 10:03 PM Access Granted 1443ASM POST 1 TS4 EXIT C11-P3-R1 40386 20322132 12/5/19 7:03 PM 12/05/19 19:03:06 Unknown 0
12/5/19 7:14 PM Access Granted 5014 ENT CSS TOR 3 RA C12-P2-R2 41691 301497 12/5/19 4:14 PM 12/05/19 16:14:39 Contractor 1
12/5/19 7:08 PM Access Granted 1443ASM POST 2 TS5 EXIT C03-P2-R1 41804 20619953 12/5/19 4:08 PM 12/05/19 16:08:23 Contractor 0
12/5/19 11:10 AM Access Granted 1443ASM POST 2 TS6 EXIT C03-P2-R2 42601 320316 12/5/19 8:10 AM 12/05/19 8:10:09 Contractor 0
12/5/19 11:13 AM Access Granted 1443ASM POST 2 TS6 EXIT C03-P2-R2 42601 320316 12/5/19 8:13 AM 12/05/19 8:13:19 Contractor 0
12/5/19 7:25 PM Access Granted 1493 ROMULUS T/S 5 EAST OUT -C23-P1-R6 44085 410614 12/5/19 4:25 PM 12/05/19 16:25:36 Contractor 0
12/5/19 10:50 AM Access Granted 1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1 44085 410614 12/5/19 7:50 AM 12/05/19 7:50:27 Contractor 1
Hello @rf27
i tried to analyse your data withou success. Grouped by the two columns i got this examples (only with nulls)
03.12.2019 20:21:00 | Access Granted | 1443ASM POST 2 TS4 EXIT C03-P3-R1 | 24808 | 20600609 | 03.12.2019 17:21:00 | 03.12.2019 | 17:21:42 | Contractor | 0 |
04.12.2019 20:25:00 | Access Granted | 1443ASM POST 2 TS5 EXIT C03-P2-R1 | 24808 | 20600609 | 04.12.2019 17:25:00 | 04.12.2019 | 17:25:29 | Contractor | 0 |
05.12.2019 20:22:00 | Access Granted | 1443ASM POST 2 TS6 EXIT C03-P2-R2 | 24808 | 20600609 | 05.12.2019 17:22:00 | 05.12.2019 | 17:22:12 | Contractor | 0 |
for others only 1
03.12.2019 18:39:00 | Access Granted | 5014 ENT CSS CORREDIZA 3 C14-P6-R1 | 41691 | 301497 | 03.12.2019 15:39:00 | 03.12.2019 | 15:39:40 | Contractor | 1 |
03.12.2019 20:10:00 | Access Granted | 5014 ENT CSS TOR 4 RA C12-P3-R2 | 41691 | 301497 | 03.12.2019 17:10:00 | 03.12.2019 | 17:10:54 | Contractor | 1 |
03.12.2019 13:17:00 | Access Granted | 5014 ENT CSS CORREDIZA 2 C14-P7-R1 | 41691 | 301497 | 03.12.2019 10:17:00 | 03.12.2019 | 10:17:22 | Contractor | 1 |
03.12.2019 18:38:00 | Access Granted | 5014 ENT CSS CORREDIZA 2 C14-P7-R1 | 41691 | 301497 | 03.12.2019 15:38:00 | 03.12.2019 | 15:38:38 | Contractor | 1 |
03.12.2019 13:16:00 | Access Granted | 5014 ENT CSS TOR 1 RA C12-P0-R2 | 41691 | 301497 | 03.12.2019 10:16:00 | 03.12.2019 | 10:16:42 | Contractor | 1 |
05.12.2019 19:14:00 | Access Granted | 5014 ENT CSS TOR 3 RA C12-P2-R2 | 41691 | 301497 | 05.12.2019 16:14:00 | 05.12.2019 | 16:14:39 | Contractor | 1 |
Jimmy
How did you use the Group by ? What Columns ?
DAX does not have a built in concept like "previous row".
To compare a time in one row to a time in the previous row you need to do 3 things
Create an index for the table in power query, using "add column"
Store the index for the current row in a variable
Use lookupvalue() to get the value you want from the previous row by looking for the index -1
In your case you would also have to check that the day in the previous row was the same day. If it was you would have the 2 datetime values you needed to use DATEDIFF() since it can compare by minute if you like.
If you need help with the DAX code, paste in a copy of your data (rather than using an image) and I'll copy and paste it into a power bi file and write some sample code for you.
I'm a personal Power Bi Trainer I learn something every time I answer a question
The Golden Rules for Power BI
Help when you know. Ask when you don't!
Hi Ken,
Is it possible you could send me an example ? I have created the index, but I'm still having issues with this..
Any help is greatly appreciated ....
Richard
How about doing a screen share ? Its easier to look into complex problems that way. Just email me a good date and time and I'll send you a meeting invitation. ken@8thfold.com
Help when you know. Ask when you don't!
Hi Ken,
Thanks for your reply... I'll give it a go..
Richard
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
8 |
User | Count |
---|---|
13 | |
12 | |
11 | |
10 | |
9 |