topic Re: Calculate table for activity pairs and time durations in sets in DAX Commands and Tips

Calculate table for activity pairs and time durations in sets

witbi — Tue, 23 Nov 2021 15:00:54 GMT

Hello all,

I have the following log data (table 1) that I am looking to transform into activity pair time durations. The new table (table 2) would show a list of activity pairs, durations and rest times (time difference between activity pairs). Additionally, if a rest time is greater than 5min this starts a new activity set.

I would be most greatful if anyone in the community could assist in pointing me in the right direction to calculate table 2 in DAX.

Thank you!

Table 1

ID	Start time	End Time	Circuit
ABC	01/11/21 09:00:41	01/11/21 09:03:25	Skipping
ABC	01/11/21 09:04:02	01/11/21 09:04:56	Squats
ABC	01/11/21 09:06:03	01/11/21 09:07:00	Push ups
ABC	01/11/21 09:09:01	01/11/21 09:10:11	Jumping jacks
ABC	01/11/21 09:10:47	01/11/21 09:12:18	Crunches
ABC	01/11/21 09:13:33	01/11/21 09:15:53	Bench dips
ABC	01/11/21 09:16:21	01/11/21 09:17:44	Step up
ABC	01/11/21 09:18:08	01/11/21 09:19:25	Plank
ABC	01/11/21 09:19:56	01/11/21 09:21:52	Back extensions
ABC	01/11/21 09:27:32	01/11/21 09:30:53	Push ups
ABC	01/11/21 09:31:57	01/11/21 09:34:15	Skipping
ABC	01/11/21 09:34:43	01/11/21 09:37:55	Crunches
ABC	01/11/21 09:43:11	01/11/21 09:44:47	Step up
ABC	01/11/21 09:45:31	01/11/21 09:48:09	Bench dips
ABC	01/11/21 09:48:55	01/11/21 09:50:02	Back extensions
ABC	01/11/21 09:50:41	01/11/21 09:51:56	Squats
ABC	01/11/21 09:52:36	01/11/21 09:54:19	Plank
ABC	01/11/21 09:54:54	01/11/21 09:57:02	Jumping jacks

Table 2

ID	Start time	End Time	Circuit start	Circuit stop	Set	Pair	Start activity duration	End activity duration	Rest time	Activity pair duration
ABC	01/11/21 09:00:41	01/11/21 09:04:56	Skipping	Squats	1	1	00:02:44	00:00:54	00:00:37	00:04:15
ABC	01/11/21 09:04:02	01/11/21 09:07:00	Squats	Push ups	1	2	00:00:54	00:00:57	00:01:07	00:02:58
ABC	01/11/21 09:06:03	01/11/21 09:10:11	Push ups	Jumping jacks	1	3	00:00:57	00:01:10	00:02:01	00:04:08
ABC	01/11/21 09:09:01	01/11/21 09:12:18	Jumping jacks	Crunches	1	4	00:01:10	00:01:31	00:00:36	00:03:17
ABC	01/11/21 09:10:47	01/11/21 09:15:53	Crunches	Bench dips	1	5	00:01:31	00:02:20	00:01:15	00:05:06
ABC	01/11/21 09:13:33	01/11/21 09:17:44	Bench dips	Step up	1	6	00:02:20	00:01:23	00:00:28	00:04:11
ABC	01/11/21 09:16:21	01/11/21 09:19:25	Step up	Plank	1	7	00:01:23	00:01:17	00:00:24	00:03:04
ABC	01/11/21 09:18:08	01/11/21 09:21:52	Plank	Back extensions	1	8	00:01:17	00:01:56	00:00:31	00:03:44
ABC	01/11/21 09:19:56	01/11/21 09:30:53	Back extensions	Push ups	1	9	00:01:56	00:03:21	00:05:40	00:10:57
ABC	01/11/21 09:27:32	01/11/21 09:34:15	Push ups	Skipping	2	1	00:03:21	00:02:18	00:01:04	00:06:43
ABC	01/11/21 09:31:57	01/11/21 09:37:55	Skipping	Crunches	2	2	00:02:18	00:03:12	00:00:28	00:05:58
ABC	01/11/21 09:34:43	01/11/21 09:44:47	Crunches	Step up	2	3	00:03:12	00:01:36	00:05:16	00:10:04
ABC	01/11/21 09:43:11	01/11/21 09:48:09	Step up	Bench dips	3	1	00:01:36	00:02:38	00:00:44	00:04:58
ABC	01/11/21 09:45:31	01/11/21 09:50:02	Bench dips	Back extensions	3	2	00:02:38	00:01:07	00:00:46	00:04:31
ABC	01/11/21 09:48:55	01/11/21 09:51:56	Back extensions	Squats	3	3	00:01:07	00:01:15	00:00:39	00:03:01
ABC	01/11/21 09:50:41	01/11/21 09:54:19	Squats	Plank	3	4	00:01:15	00:01:43	00:00:40	00:03:38
ABC	01/11/21 09:52:36	01/11/21 09:57:02	Plank	Jumping jacks	3	5	00:01:43	00:02:08	00:00:35	00:04:26

Re: Calculate table for activity pairs and time durations in sets

VahidDM — Tue, 23 Nov 2021 22:57:25 GMT

Hi witbi

How did you calculate the Set numbers?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: Calculate table for activity pairs and time durations in sets

witbi — Wed, 24 Nov 2021 08:52:05 GMT

Hi VahidDM , thanks for your question.

The set number is determined by looking up if the rest time between two circuit steps is >5min. When a new set is identified the set Pair count resets so we can also see how many pairs are in a set.

Am still getting my head around Power BI and DAX. Thanks for any assistance you can provide!

Re: Calculate table for activity pairs and time durations in sets

Anonymous — Fri, 26 Nov 2021 08:08:31 GMT

Hi witbi ,

Try my code to create a calculated table.

Table 2 = VAR _SUMMARIZE = SUMMARIZE ( 'Table 1', 'Table 1'[ID], 'Table 1'[Start time], 'Table 1'[End Time], 'Table 1'[Circuit], "RANK", RANKX ( FILTER ( ALL ( 'Table 1' ), 'Table 1'[ID] = EARLIER ( 'Table 1'[ID] ) ), 'Table 1'[Start time], , ASC ) ) VAR _ADD = ADDCOLUMNS ( _SUMMARIZE, "New End Time", MINX ( FILTER ( _SUMMARIZE, [ID] = EARLIER ( [ID] ) && [End Time] > EARLIER ( [End Time] ) ), [End Time] ), "Circuit stop", MAXX ( FILTER ( _SUMMARIZE, [ID] = EARLIER ( [ID] ) && [RANK] = EARLIER ( [RANK] ) + 1 ), [Circuit] ), "Start activity duration", 'Table 1'[End Time] - 'Table 1'[Start time], "End activity duration", VAR _MINSTART = MINX ( FILTER ( _SUMMARIZE, [ID] = EARLIER ( [ID] ) && [Start time] > EARLIER ( [Start time] ) ), [Start time] ) VAR _MINEND = MINX ( FILTER ( _SUMMARIZE, [ID] = EARLIER ( [ID] ) && [End Time] > EARLIER ( [End Time] ) ), [End Time] ) RETURN _MINEND - _MINSTART, "Rest time", [End Time] - MINX ( FILTER ( _SUMMARIZE, [ID] = EARLIER ( [ID] ) && [Start time] > EARLIER ( [Start time] ) ), [Start time] ), "Activity pair duration", MINX ( FILTER ( _SUMMARIZE, [ID] = EARLIER ( [ID] ) && [End Time] > EARLIER ( [End Time] ) ), [End Time] ) - 'Table 1'[Start time] ) VAR _ADD2 = ADDCOLUMNS ( _ADD, "Set", COUNTAX ( FILTER ( _ADD, [ID] = EARLIER ( [ID] ) && [RANK] <= EARLIER ( [RANK] ) && MINUTE ( [Rest time] ) >= 5 ), [ID] ) + 1 ) VAR _RESULT1 = SUMMARIZE ( FILTER ( _ADD2, [Circuit stop] <> BLANK () ), [ID], [Start time], [New End Time], [Circuit], [Circuit stop], [Start activity duration], [End activity duration], [Rest time], [Activity pair duration], [Set], "Pair", RANKX ( FILTER ( _ADD2, [ID] = EARLIER ( [ID] ) && [Set] = EARLIER ( [Set] ) ), [Start time], , ASC ) ) RETURN _RESULT1

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Calculate table for activity pairs and time durations in sets

witbi — Mon, 29 Nov 2021 14:06:08 GMT

Hello Anonymous - this is spot on! I'm very grateful for your clear solution proposal. The breakdown to show time-based analysis is most helpful. Many thanks!