Aggregating Duration/Time data

nblair

Your scenario has some interesting wrinkles in it - a sort of streaming analytics with status updates every 1-10 seconds.  I suspect you'll have some other issues around perfromance, refresh, edge cases such as machines that stop reporting etc.

To get started, and assuming your Machines don't change state on every read, you could add a "Change_State_Flag" column in Power QUery as you import the data - then you can easily filter those (many fewer?) records that indicate a change in the "_Value" or a different "_MachineID" - e.g.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("3c5LCsAgDEXRrUjGgonxE91DofPg/rfRVqdK5p0+DryrCgU8UAw5RKTiuKfWqbr7emeG4RXqGeAEhGdBU7D1ka2PXaVYlWJWilUpVqX8o7ItkDaA8icSroPxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [_MachineId = _t, _TimeStamp = _t, _Value = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"_MachineId", Int64.Type}, {"_TimeStamp", type datetime}, {"_Value", Int64.Type}}),
    SortedRows = Table.Sort(ChangedType,{{"_MachineId", Order.Ascending}, {"_TimeStamp", Order.Ascending}}),
    AddedIndex = Table.AddIndexColumn(SortedRows, "Index", 0, 1),
    AddedChangedStateFlag = Table.AddColumn(AddedIndex, "Changed_State_Flag", each if [Index] = 0 or ([_MachineId] <> AddedIndex[_MachineId]{[Index]- 1} or [_Value] <> AddedIndex[_Value]{[Index]- 1}) then 1 else 0 ),
    SetFlagAsNumber = Table.TransformColumnTypes(AddedChangedStateFlag,{{"Changed_State_Flag", Int64.Type}})
in
    SetFlagAsNumber

Then you could add some Measures for the Start and End Timestamps on a change of state (e.g. when does Machine 1 go from Value 3 to 4 and when does it then change from 4 for another Value).  Then you can get a Duration (in seconds for your current test data) - e.g.

State Start Timestamp =
CALCULATE (
    MIN ( Table1[_TimeStamp] ),
    FILTER (
        Table1,
        Table1[Changed_State_Flag] = 1
            && Table1[_Value] = MAX ( Table1[_Value] )
            && Table1[_MachineID] = MAX ( Table1[_MachineId] )
            && Table1[_TimeStamp] = MAX ( Table1[_TimeStamp] )
    )
)

State End Timestamp =
CALCULATE (
    MIN ( Table1[_TimeStamp] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Changed_State_Flag] = 1
            && Table1[_Value] <> MAX ( Table1[_Value] )
            && Table1[_MachineID] = MAX ( Table1[_MachineId] )
            && Table1[_TimeStamp] > MAX ( Table1[_TimeStamp] )
    )
)

State Duration Seconds =
IF (
    ISBLANK ( [State Start TimeStamp] ) || ISBLANK ( [State End Timestamp] ),
    BLANK (),
    ( [State End TimeStamp] - [State Start Timestamp] )
        * 24
        * 60
        * 60
)

Add them to a table etc. with a filter on State_Change_Flag  = 1, and you'll get close to what you were proposing though I'm not sure how to present it graphically.