Forum Discussion

xbillx81's avatar
xbillx81
Regular Visitor
3 years ago
Solved

split event row into multiple rows by hour

Need to calculate the number of minutes in each hour block a device was in error state.  I gather from research this could be done with DAX calculate or generateseries but struggling for how to implement.  

https://1drv.ms/u/s!Ajwjsn7bUmgJwB5St9WqA8rimPTa?e=wpA7dc    <pbix file is here 

Source data 

 

DeviceIDEvent StartEvent EndCategory
ABC12312/1/2022 1:0012/1/2022 20:00Cat1
CDE45611/28/2022 17:4811/29/2022 3:24Cat2

 

Expected Output

DeviceIDEvent StartEvent EndCategoryDuration_MinutesPercent
ABC12312/1/2022 2:0012/1/2022 3:00Cat160100%
ABC12312/1/2022 3:0012/1/2022 4:00Cat160100%
…     
CDE45611/28/2022 17:0011/28/2022 18:00Cat21220%
CDE45611/28/2022 18:0011/28/2022 19:00Cat260100%
CDE45611/28/2022 19:0011/28/2022 20:00Cat260100%
…     
CDE45611/29/2022 3:0011/29/2022 4:00Cat22440%
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi xbillx81 
    Please refer to attached sample file with the solution

    Time = 
VAR StartTime = DATEVALUE ( MIN ( 'Table'[Event Start] ) )
VAR EndTime = DATEVALUE ( MAX ( 'Table'[Event End] ) ) + TIME ( 23, 0, 0 )
RETURN
    GENERATE (
        SELECTCOLUMNS ( 
            GENERATESERIES ( StartTime, EndTime, TIME ( 1, 0, 0 ) ),
            "Start Time",
            [Value]
        ),
        ROW ( "End Time", [Start Time] + TIME ( 1, 0, 0 ) )
    )
    Duration = 
VAR EventStart = SELECTEDVALUE ( 'Table'[Event Start] )
VAR EventEnd = SELECTEDVALUE ( 'Table'[Event End] )
VAR StartTime = SELECTEDVALUE ( 'Time'[Start Time] )
VAR EndTime = SELECTEDVALUE ( 'Time'[End Time] )
VAR Result =
    DATEDIFF (
        MAX ( EventStart, StartTime ),
        MIN ( EventEnd, EndTime ),
        MINUTE
    )
RETURN
    IF ( Result > 0, Result )
    % Duration = [Duration]/60

4 Replies

  • xbillx81's avatar
    xbillx81
    Regular Visitor
    3 years ago

    This is great, thank you.  Simpler than I thought, I was over complicating it. 

     

     

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi xbillx81 
    Please refer to attached sample file with the solution

    Time = 
VAR StartTime = DATEVALUE ( MIN ( 'Table'[Event Start] ) )
VAR EndTime = DATEVALUE ( MAX ( 'Table'[Event End] ) ) + TIME ( 23, 0, 0 )
RETURN
    GENERATE (
        SELECTCOLUMNS ( 
            GENERATESERIES ( StartTime, EndTime, TIME ( 1, 0, 0 ) ),
            "Start Time",
            [Value]
        ),
        ROW ( "End Time", [Start Time] + TIME ( 1, 0, 0 ) )
    )
    Duration = 
VAR EventStart = SELECTEDVALUE ( 'Table'[Event Start] )
VAR EventEnd = SELECTEDVALUE ( 'Table'[Event End] )
VAR StartTime = SELECTEDVALUE ( 'Time'[Start Time] )
VAR EndTime = SELECTEDVALUE ( 'Time'[End Time] )
VAR Result =
    DATEDIFF (
        MAX ( EventStart, StartTime ),
        MIN ( EventEnd, EndTime ),
        MINUTE
    )
RETURN
    IF ( Result > 0, Result )
    % Duration = [Duration]/60
  • xbillx81's avatar
    xbillx81
    Regular Visitor
    3 years ago

    Ok i've spent a few weeks working with this and the way this displays in a table report is perfect.  however, i need to perform more calculations and analysis and joins on that table and I cannot for the life of me get it to export or create into a new table.  New question is how can I get the expect output into a table instead of just into the table view report.  I'm working with a about 300k rows and the report is hitting max row limits. 

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      Hi xbillx81 
      Please refer to attached updated sample file

      Table 2 = 
ADDCOLUMNS (
    SELECTCOLUMNS (
        GENERATE (
            'Table',
            VAR DeviceCategoryTable = 
                CALCULATETABLE ( 
                    'Table',
                    ALLEXCEPT ( 'Table', 'Table'[Category], 'Table'[DeviceID] )
                )
            VAR EventStart = MINX ( DeviceCategoryTable, 'Table'[Event Start] )
            VAR EventEnd = MAXX ( DeviceCategoryTable, 'Table'[Event End] )
            VAR StartMinute = MINUTE ( EventStart )
            VAR StartTime = EventStart - TIME ( 0, StartMinute, 0 )
            VAR EndMinute = MINUTE ( EventEnd )
            VAR EndHour = IF ( EndMinute = 0, 1, 0 )
            VAR EndTime = EventEnd - TIME ( EndHour, EndMinute, 0 )
            VAR TimeTable =
                GENERATE (
                    SELECTCOLUMNS ( 
                        GENERATESERIES ( StartTime, EndTime, TIME ( 1, 0, 0 ) ),
                        "Start Time",
                        [Value]
                    ),
                    ROW ( "End Time", [Start Time] + TIME ( 1, 0, 0 ) )
                )
            RETURN
                TimeTable
        ),
        "DeviceID", [DeviceID],
        "Start Time", [Start Time],
        "End Time", [End Time],
        "Category", [Category],
        "Duration", 
        VAR Difference =
            DATEDIFF (
                MAX ( [Event Start], [Start Time] ),
                MIN ( [Event End], [End Time] ),
                MINUTE
            )
        RETURN
            IF ( Difference > 0, Difference )
    ),
    "% Duration", 
    [Duration] / 60
)