Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
Expand all | Collapse all

Looking for DAX help totaling time of overlapping events.

  • 1.  Looking for DAX help totaling time of overlapping events.

    Bronze Contributor
    Posted Jul 11, 2022 03:01 PM

    Hello,

     

    I have a problem I'm trying to solve with a DAX measure but have not been able to come up with a formula that will work.  I have parent events that have a varied number of child events.  I'm trying to accumulate duration of these child events grouped by parent but need to exclude overlapping time.

     

    Example of data for a single parent would look like this. The Non-Overlapping column does not exist. I'm looking for how I can calculate the total of non-overlapping time for a parent.  It does not matter which of the children the overlap is cut out of. 

    ParentID

    ChildID

    Start

    End

    Record Duration

    Non-overlapping

    150

    1

    0

    20

    20

    20

    150

    2

    5

    10

    5

    0

    150

    3

    10

    25

    15

    5

    150

    4

    30

    60

    30

    30

    150

    5

    35

    40

    5

    0

    150

    6

    45

    55

    10

    0

    Total 

    85

    55

     

    Graphically the children for this parent would look like this:

    I can get the duration of each individual event by End – Start. However how would I go about getting the Non-Overlapping time that should total 55 time units? I'm looking for the duration of the red line above.



    ------------------------------
    Steve Bolton
    Sr. Business Analyst
    ------------------------------


  • 2.  RE: Looking for DAX help totaling time of overlapping events.

    Posted Jul 12, 2022 09:52 AM
    Steve,

    This is a problem I had to solve for a cube-based reporting system over help-desk ticket data. The SQL-based solution I found was based on the solution to a puzzle that Itzik Ben-Gan published back in 2011. It may be the case that it will be easier to leverage SQL to manipulate your data set prior to import, to add attributes to the set that will allow you to construct the visuals you desire. You may also be able to take the pattern of the SQL solution and use it to guide an implementation in M and DAX.

    Here is the link to the article that provided several solutions to the puzzle: Solutions to Packing Date and Time Intervals Puzzle (itprotoday.com)

    Warm regards,

    Clayton

    ------------------------------
    Clayton Groom
    @SQLMonger
    ------------------------------



  • 3.  RE: Looking for DAX help totaling time of overlapping events.

    Bronze Contributor
    Posted Jul 13, 2022 08:26 AM
    Hi Clayton,

    Thanks for the reply.  Unfortunately I do not have SQL available as part of this workflow.  This reads directly from log files and I have only M and DAX available.

    I looked at the link you provided and do understand the concepts of what they are doing in SQL. I'm fairly fluent with SQL, but am a novice with DAX and intermediate with M.  I'm still struggling with finding the DAX or M code to do this.

    Steve

    ------------------------------
    Steve Bolton
    Sr. Business Analyst
    ------------------------------



  • 4.  RE: Looking for DAX help totaling time of overlapping events.

    Posted Jul 14, 2022 10:38 AM
    Steve,

    This challenge intrigues me. I'm going to see what I can do to help you. This may take a while given my schedule, but I'm going to post what I figure out as I go...
    The SQL approach to solving the date packing problem relies on Window functions. Window functions in SQL are an area of interest to me, so replicating them in Power BI is a fun puzzle. Here are resources on replicating Window functions in Power Query or DAX that I plan on using as a starting point:
    Replicating SQL windowing functions in Power Query. | Purple Frog Systems
    From SQL to DAX- 'Lead' and 'Lag' window functions – Gal Love Data (xuanalytics.com)
    Solved: DAX SQL window function equivalent - Microsoft Power BI Community
    SQL equivalent Power BI DAX functions - Part 1 (datacaffee.com)
    SQL equivalent Power BI DAX functions – Part 2 (datacaffee.com)

    More to follow...

    Clayton

    ------------------------------
    Clayton Groom
    @SQLMonger
    ------------------------------



  • 5.  RE: Looking for DAX help totaling time of overlapping events.

    Posted Jul 17, 2022 07:30 PM
    Veja se o pbix aqui ajuda



    ------------------------------
    Vilmar Santos
    ------------------------------



  • 6.  RE: Looking for DAX help totaling time of overlapping events.

    Posted 14 days ago
    Where you able to figure out how to do this? I'm having the same problem and struggling to solve.

    ------------------------------
    Elyse Smith
    85283
    ------------------------------



  • 7.  RE: Looking for DAX help totaling time of overlapping events.

    Bronze Contributor
    Posted 14 days ago
    Elyse, I have not figured it out yet.  I have an idea for doing this in Power Query and may have time this weekend to test that out.  I'll post results here once I've tried it.


    ------------------------------
    Steve Bolton
    Sr. Business Analyst
    ------------------------------



  • 8.  RE: Looking for DAX help totaling time of overlapping events.

    Top Contributor
    Posted 11 days ago
    Edited by Sam Duval 11 days ago
    Do you have power automate at your disposal? I keep trying to think of a way to do it in power query and/or BI but keep coming back to SQL based solutions.

    Anyways if you have power automate, there is an action item that can run a query against a power bi dataset, so you can use power bi to import the log files, power automate to port the data over to SQL and then reimport the cleansed data back to power bi. This is also dependent on the number of rows and columns you have in the dataset. There's a limit that's based on the ratio of columns x rows.
    @Elyse Smith


    ------------------------------
    Sam Duval
    Data Quality Analyst
    Element Financial
    Indianapolis IN
    ------------------------------



  • 9.  RE: Looking for DAX help totaling time of overlapping events.

    Posted 10 days ago
    If you can treat the situation as one day at a time, parse this into a table of Person, timeblock, taken/not taken, make it distinct, and then count the rows that are taken?  Essentially you'd be unpivoting the start and end times, with whichever block size you needed to count?   If you need to count by the minute across a year that's not very efficient, but it could be done without processing logic.

    ------------------------------
    Elena Schott
    Sr. Business Analyst
    Aon - San Francisco, CA
    ------------------------------



  • 10.  RE: Looking for DAX help totaling time of overlapping events.

    Bronze Contributor
    Posted 9 days ago
    Elena, thanks for the suggestion.  However I don't think that will work in this case.  This is involving log files from a computer process that is tracked in milliseconds and could span multiple seconds for the full process. I'm currently working with someone to develop and test out a function to be used in the power query data load step.  We've got a draft built but testing is needed yet.  I'll post results here once we get further with it.


    ------------------------------
    Steve Bolton
    Sr. Business Analyst
    ------------------------------



  • 11.  RE: Looking for DAX help totaling time of overlapping events.

    Top Contributor
    Posted 8 days ago
    Hi all,

    The idea is to Count Time Values having at least 1 event, multiplying this result by 5 gives the expected duration

    1 - Generating Time values Axis

    You have to create a new table in PBI with this formula

    Times = GENERATESERIES(0, MAX('Table'[End])+5,5)


    2 - The duration measure

    Duration = 
    SUMX (
        ADDCOLUMNS (
            VALUES ( Times[Time Value] ),
            "Count of ranges",
                VAR _Time = Times[Time Value]
                RETURN
                    IF (
                        CALCULATE (
                            COUNTROWS ( 'Table' ),
                            _Time < 'Table'[End]
                                && _Time >= 'Table'[Start]
                        ) >= 1,
                        5 -- Because each time range is 5 units
                    )
        ),
        [Count of ranges]
    ) + 0

    Here is the result I get


    I hope this is a good start for you


    ------------------------------
    Nicolas MENDEZ
    Data & BI consultant
    Becom Consulting
    ------------------------------



  • 12.  RE: Looking for DAX help totaling time of overlapping events.

    Posted 8 days ago
    Edited by Jesica Morgan 12 hours ago
    The time it takes to develop an app depends on several factors, including the complexity of the app, the number of screens and features, and the number of roles in the software https://mlsdev.com/blog/how-to-create-a-video-streaming-website. Depending on the complexity of the app, the development time can take two to three weeks, or it could take up to four and a half months. The time it takes to develop an app will also depend on the skill level of the development team and the type of operation required.
    ------------------------------
    Jesica Morgan
    ------------------------------