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