Help displaying age of a off signal

[Anonymous]

Hello, Im trying to answer the question, what are the top 20 devices that have been offline for the longest. How could i go about doing this, the below is some sample data im loading.

Device ID	Event	Date/Time
1	On	08-MAR-19 12:39:02
1	Off	08-MAR-19 12:49:02
2	On	08-MAR-19 12:39:02
2	Off	08-MAR-19 18:39:02
2	On	08-MAR-19 19:39:02
3	On	07-MAR-19 10:39:02

In this example, i would know that:
Device 1 was Off
Device 2 is On, has been for X Hours which is less that Device 3
Device 3 is On, and have been for X Hours which is longer than Device 2 

Im wanting to visually represent this in Power BI in a Top 20 bar graph for devices that have been Off for the longest. 

[Anonymous · Accepted solution]

Hi @Anonymous ,

You could create a calculated column as below:

Off Since (Hours) = 
VAR myEventDate = DeviceLog[Date/Time]
var myDeviceID = DeviceLog[Device ID]
var myEvent = DeviceLog[Event]
VAR myNextDate = MINX(FILTER('DeviceLog', myEvent = "Off" && DeviceLog[Event] = "On" && DeviceLog[Device ID] = myDeviceID && DeviceLog[Date/Time] > EARLIER(DeviceLog[Date/Time])),DeviceLog[Date/Time])
var OffSinceHours = IF(myEvent = "Off",DATEDIFF(DeviceLog[Date/Time],IF(ISBLANK(myNextDate),TODAY(),myNextDate),HOUR),BLANK())
return OffSinceHours

This column would give you for how long was the status Off. In case there is no "On" after an "Off" event, the code considers current date. You could replace TODAY() from the code to suit your needs.

Later add this field to the bar graph and select the Top N filter to show the top 20 values based on the computed column.

Regards,

Chetan

[d_gosbell]

You could use a measure like the following:

Days Offline = 
var _maxDatePerDevice = 
    ADDCOLUMNS(
        SUMMARIZE(Table1, Table1[Device ID],"MaxDate",max(Table1[Date/Time]))
        ,"Event"
        , LOOKUPVALUE(Table1[Event], Table1[Device ID],[Device ID],Table1[Date/Time], [MaxDate])
    )
var _offDevices = FILTER(_maxDatePerDevice,[Event] = "Off")
return AVERAGEX(_offDevices, DATEDIFF([MaxDate], NOW(),DAY))