split event row into multiple rows by hour

[xbillx81]

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 

DeviceID	Event Start	Event End	Category
ABC123	12/1/2022 1:00	12/1/2022 20:00	Cat1
CDE456	11/28/2022 17:48	11/29/2022 3:24	Cat2

Expected Output

DeviceID	Event Start	Event End	Category	Duration_Minutes	Percent
ABC123	12/1/2022 2:00	12/1/2022 3:00	Cat1	60	100%
ABC123	12/1/2022 3:00	12/1/2022 4:00	Cat1	60	100%
…
CDE456	11/28/2022 17:00	11/28/2022 18:00	Cat2	12	20%
CDE456	11/28/2022 18:00	11/28/2022 19:00	Cat2	60	100%
CDE456	11/28/2022 19:00	11/28/2022 20:00	Cat2	60	100%
…
CDE456	11/29/2022 3:00	11/29/2022 4:00	Cat2	24	40%

[xbillx81]

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]

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
)

[xbillx81]

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

[tamerj1 · Accepted solution]

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