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 imple...
  • 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
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
)