Count on consective hours that contain specific flags

[kararandle2710]

I'm trying find a way to count consective row hours that are greater than two and also contain specfic flags.  So in the table below the count for Parameter C and N would each have a count of 1.  Any help would be greatly appreciated, as I'm not sure if I need to do this in power bi or if I can create a measure.

Kara

Parameter	Day	Hour	Flag
C	1/1/2019	1
C	1/1/2019	2
C	1/1/2019	3	m
C	1/1/2019	4
C	1/1/2019	5	m
C	1/1/2019	6
C	1/1/2019	7
C	1/1/2019	8	m
C	1/1/2019	9	m
C	1/1/2019	10
C	1/1/2019	11
C	1/1/2019	12
C	1/1/2019	13
C	1/1/2019	14
C	1/1/2019	15
C	1/1/2019	16
C	1/1/2019	17	m
C	1/1/2019	18	m
C	1/1/2019	19	m
C	1/1/2019	20
C	1/1/2019	21
C	1/1/2019	22
C	1/1/2019	23
N	1/1/2019	1
N	1/1/2019	2
N	1/1/2019	3	m
N	1/1/2019	4
N	1/1/2019	5	m
N	1/1/2019	6
N	1/1/2019	7
N	1/1/2019	8	m
N	1/1/2019	9	m
N	1/1/2019	10
N	1/1/2019	11
N	1/1/2019	12
N	1/1/2019	13
N	1/1/2019	14
N	1/1/2019	15
N	1/1/2019	16
N	1/1/2019	17	m
N	1/1/2019	18	m
N	1/1/2019	19	m
N	1/1/2019	20
N	1/1/2019	21
N	1/1/2019	22
N	1/1/2019	23

[v-xuding-msft]

Hi @kararandle2710 ,

I'm a little confused about the result. Can you please share more to us? How can we get the count of 1 per parameters? Do you mean the max consecutive hours(the rows of 17,18, 19 hours for C and N)? Can you please attach a screenshot of your expected result?

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kararandle2710]

Thank you for the response. @v-xuding-msft 

The hours column is the time of day, so the flag or error would only be considered 1 time if it occurs more than three hours in a row.  We have total hours online but during that time we are looking to find the count of errors to get  the percentage of errors for that day or month.  Does that help?  See below.

Parameter	Day	Flag(Error) Count	Flag	Total Numbers online	Error % (Flag Count/Total Numbers online)
C	1/1/2019	1	m	12	8%
N	1/1/2019	1	m	18	6%

[kararandle2710]

@v-xuding-msftI was wondering if you had an opportunity to look at this again.  Any help would be greatly appreciated.

Thank you!

Kara