Calculate table for activity pairs and time durations in sets

[witbi]

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

[Anonymous · Accepted solution]

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.

[witbi]

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! 

[Anonymous · Accepted solution]

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.

[witbi · Accepted solution]

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!

[VahidDM]

Hi @witbi 

How did you calculate the Set numbers?

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