Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.

Reply
witbi
Helper I
Helper I

Calculate table for activity pairs and time durations in sets

‎11-23-2021 07:00 AM

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

IDStart timeEnd TimeCircuit
ABC01/11/21 09:00:4101/11/21 09:03:25Skipping
ABC01/11/21 09:04:0201/11/21 09:04:56Squats
ABC01/11/21 09:06:0301/11/21 09:07:00Push ups
ABC01/11/21 09:09:0101/11/21 09:10:11Jumping jacks
ABC01/11/21 09:10:4701/11/21 09:12:18Crunches
ABC01/11/21 09:13:3301/11/21 09:15:53Bench dips
ABC01/11/21 09:16:2101/11/21 09:17:44Step up
ABC01/11/21 09:18:0801/11/21 09:19:25Plank
ABC01/11/21 09:19:5601/11/21 09:21:52Back extensions
ABC01/11/21 09:27:3201/11/21 09:30:53Push ups
ABC01/11/21 09:31:5701/11/21 09:34:15Skipping
ABC01/11/21 09:34:4301/11/21 09:37:55Crunches
ABC01/11/21 09:43:1101/11/21 09:44:47Step up
ABC01/11/21 09:45:3101/11/21 09:48:09Bench dips
ABC01/11/21 09:48:5501/11/21 09:50:02Back extensions
ABC01/11/21 09:50:4101/11/21 09:51:56Squats
ABC01/11/21 09:52:3601/11/21 09:54:19Plank
ABC01/11/21 09:54:5401/11/21 09:57:02Jumping jacks



Table 2

IDStart timeEnd TimeCircuit startCircuit stopSetPairStart activity durationEnd activity durationRest timeActivity pair duration
ABC01/11/21 09:00:4101/11/21 09:04:56SkippingSquats1100:02:4400:00:5400:00:3700:04:15
ABC01/11/21 09:04:0201/11/21 09:07:00SquatsPush ups1200:00:5400:00:5700:01:0700:02:58
ABC01/11/21 09:06:0301/11/21 09:10:11Push upsJumping jacks1300:00:5700:01:1000:02:0100:04:08
ABC01/11/21 09:09:0101/11/21 09:12:18Jumping jacksCrunches1400:01:1000:01:3100:00:3600:03:17
ABC01/11/21 09:10:4701/11/21 09:15:53CrunchesBench dips1500:01:3100:02:2000:01:1500:05:06
ABC01/11/21 09:13:3301/11/21 09:17:44Bench dipsStep up1600:02:2000:01:2300:00:2800:04:11
ABC01/11/21 09:16:2101/11/21 09:19:25Step upPlank1700:01:2300:01:1700:00:2400:03:04
ABC01/11/21 09:18:0801/11/21 09:21:52PlankBack extensions1800:01:1700:01:5600:00:3100:03:44
ABC01/11/21 09:19:5601/11/21 09:30:53Back extensionsPush ups1900:01:5600:03:2100:05:4000:10:57
ABC01/11/21 09:27:3201/11/21 09:34:15Push upsSkipping2100:03:2100:02:1800:01:0400:06:43
ABC01/11/21 09:31:5701/11/21 09:37:55SkippingCrunches2200:02:1800:03:1200:00:2800:05:58
ABC01/11/21 09:34:4301/11/21 09:44:47CrunchesStep up2300:03:1200:01:3600:05:1600:10:04
ABC01/11/21 09:43:1101/11/21 09:48:09Step upBench dips3100:01:3600:02:3800:00:4400:04:58
ABC01/11/21 09:45:3101/11/21 09:50:02Bench dipsBack extensions3200:02:3800:01:0700:00:4600:04:31
ABC01/11/21 09:48:5501/11/21 09:51:56Back extensionsSquats3300:01:0700:01:1500:00:3900:03:01
ABC01/11/21 09:50:4101/11/21 09:54:19SquatsPlank3400:01:1500:01:4300:00:4000:03:38
ABC01/11/21 09:52:3601/11/21 09:57:02PlankJumping jacks3500:01:4300:02:0800:00:3500:04:26
Message 1 of 5
764 Views
0
2 ACCEPTED SOLUTIONS
Anonymous
Not applicable
In response to witbi

‎11-26-2021 12:08 AM

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.

1.png

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.

View solution in original post

Calculate table for activity pairs and time durations in sets.pbix
Message 4 of 5
624 Views
0
witbi
Helper I
Helper I
In response to Anonymous

‎11-29-2021 06:06 AM

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!

View solution in original post

Message 5 of 5
569 Views
0
4 REPLIES 4
witbi
Helper I
Helper I

‎11-24-2021 12:52 AM

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! 

Message 3 of 5
665 Views
0
Anonymous
Not applicable
In response to witbi

‎11-26-2021 12:08 AM

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.

1.png

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.

Calculate table for activity pairs and time durations in sets.pbix
Message 4 of 5
625 Views
0
witbi
Helper I
Helper I
In response to Anonymous

‎11-29-2021 06:06 AM

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!

Message 5 of 5
570 Views
0
VahidDM
Super User
Super User

‎11-23-2021 02:57 PM

Hi @witbi 

 

How did you calculate the Set numbers?

 


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

 

 

Message 2 of 5
691 Views
0

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

View All