## Desktop

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

# I want to calculate the top 3 consecutive outages

The base data looks like this

 Creation Date Time Site Name (CRM adress 3) 16/05/2018 1 X 16/05/2018 2 X 16/05/2018 3 X 16/05/2018 4 X 16/05/2018 5 X 16/05/2018 13 X 16/05/2018 14 X 16/05/2018 15 X 16/05/2018 16 X 01/05/2018 1 Y 06/05/2018 2 Y 07/05/2018 3 Y 08/05/2018 4 Y 12/05/2018 3 Y 13/05/2018 2 Y 13/05/2018 4 Y 14/05/2018 2 Y 15/05/2018 3 Y 20/05/2018 4 Y 24/05/2018 3 Y 24/05/2018 4 Y 25/05/2018 3 Y 30/05/2018 4 Y 16/05/2018 1 Z 18/05/2018 3 Z 19/05/2018 3 Z 23/05/2018 3 Z 28/05/2018 3 Z

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

The Base data looks like this

 Creation Date Time Site Name (CRM adress 3) 16/05/2018 1 X 16/05/2018 2 X 16/05/2018 3 X 16/05/2018 4 X 16/05/2018 5 X 16/05/2018 13 X 16/05/2018 14 X 16/05/2018 15 X 16/05/2018 16 X 01/05/2018 1 Y 06/05/2018 2 Y 07/05/2018 3 Y 08/05/2018 4 Y 12/05/2018 3 Y 13/05/2018 2 Y 13/05/2018 4 Y 14/05/2018 2 Y 15/05/2018 3 Y 20/05/2018 4 Y 24/05/2018 3 Y 24/05/2018 4 Y 25/05/2018 3 Y 30/05/2018 4 Y 16/05/2018 1 Z 18/05/2018 3 Z 19/05/2018 3 Z 23/05/2018 3 Z 28/05/2018 3 Z

I want to caluclate the list of top 3 outages

The result should look like this

 Site Date Number of Cosecutive Hours Missing Hours missing X 16-May-18 5 Between 1 am and 5 am X 16-May-18 4 Between 13 pm to 16 pm Y 24-May-18 2 Between 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}}),
(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.