Reply
Frequent Visitor
Posts: 4
Registered: ‎11-26-2018

I want to calculate the top 3 consecutive outages

 

The base data looks like this 

 

Creation DateTimeSite Name (CRM adress 3)
16/05/20181X
16/05/20182X
16/05/20183X
16/05/20184X
16/05/20185X
16/05/201813X
16/05/201814X
16/05/201815X
16/05/201816X
01/05/20181Y
06/05/20182Y
07/05/20183Y
08/05/20184Y
12/05/20183Y
13/05/20182Y
13/05/20184Y
14/05/20182Y
15/05/20183Y
20/05/20184Y
24/05/20183Y
24/05/20184Y
25/05/20183Y
30/05/20184Y
16/05/20181Z
18/05/20183Z
19/05/20183Z
23/05/20183Z
28/05/20183Z

 

I want to calculate top 3 consecutive outages by consecutive hours on any single day

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The Base data looks like this

Creation DateTimeSite Name (CRM adress 3)
16/05/20181X
16/05/20182X
16/05/20183X
16/05/20184X
16/05/20185X
16/05/201813X
16/05/201814X
16/05/201815X
16/05/201816X
01/05/20181Y
06/05/20182Y
07/05/20183Y
08/05/20184Y
12/05/20183Y
13/05/20182Y
13/05/20184Y
14/05/20182Y
15/05/20183Y
20/05/20184Y
24/05/20183Y
24/05/20184Y
25/05/20183Y
30/05/20184Y
16/05/20181Z
18/05/20183Z
19/05/20183Z
23/05/20183Z
28/05/20183Z

 

I want to caluclate the list of top 3 outages

 

The result should look like this

 

SiteDate Number of Cosecutive Hours MissingHours missing
X16-May-185Between 1 am and 5 am
X16-May-184Between 13 pm to 16 pm
Y24-May-182Between 3 am and 4 am

 

 
How can I achieve this using Power BI?

Rgds

Amit

Member
Posts: 104
Registered: ‎08-16-2017

Re: I want to calculate the top 3 consecutive outages

[ Edited ]

I've created a query that should do the job. Here's the M code to reproduce (just paste into your Advanced Editor).

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddJBCoAgEIXhu7gW8o2j2U0q8f7XKFCipufCzcfwwwNrdchLSIsEFOcd7re75g0L58hZOSfOmFQwyWDWyY8HmElHZztp8GomDS5mUmcIvUak7Q+/IsqvE21LoBFRfq38mrcjb/8+xdm5mMjgjbJEziTSLg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Creation Date" = _t, Time = _t, #"Site Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", Int64.Type}, {"Site Name", type text}, {"Creation Date", type text}}),
    #"Added Boole Column" = Table.AddColumn(#"Changed Type", "Boole",
                                (C) => List.Contains(Table.SelectRows(#"Changed Type",
                                           each C[Creation Date] = [Creation Date] and C[Site Name] = [Site Name])[Time], C[Time] + 1), type logical),
    #"Added Grouping Column" = Table.AddColumn(#"Added Boole Column", "Group",
each Table.RowCount(Table.SelectRows(#"Added Boole Column",
(C) => C[Creation Date] = [Creation Date] and C[Time] < [Time] and C[Site Name] = [Site Name] and C[Boole] = false)), Int64.Type), #"Grouped Rows" = Table.Group(#"Added Grouping Column",
{"Creation Date", "Site Name", "Group"},
{{"Count", each Table.RowCount(_), type number}, {"Min", each List.Min([Time]), type number}, {"Max", each List.Max([Time]), type number}}), #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Count", Order.Descending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Group"}), #"Kept First 3 Rows" = Table.FirstN(#"Removed Columns",3) in #"Kept First 3 Rows"

The first step is to calculate a Boolean (True/False) column which determines whether the current hour is contiguous with the next hour. With that in hand, I group the outages into contiguous pieces by counting the FALSE values prior to the current time on that day. Using the Group column I can group by date, site, and group using the count aggregator to get the number of hours and the max and min to get the endpoints.

 

Contiguous.png