Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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