cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
xbillx81
Regular Visitor

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%
1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @xbillx81 
Please refer to attached sample file with the solution

1.png2.png3.png

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

View solution in original post

4 REPLIES 4
xbillx81
Regular Visitor

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. 

Hi @xbillx81 
Please refer to attached updated sample file

1.png

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
Regular Visitor

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

 

 

tamerj1
Super User
Super User

Hi @xbillx81 
Please refer to attached sample file with the solution

1.png2.png3.png

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors