cancel
Showing results for
Did you mean:
Frequent Visitor

## Find Consecutive Rows

Hi everyone,

I want to find 2 and 3 consecutive rows where Index equals 1 and show the results as below.

Can you please help me. I'd like to use DAX, not power query as I can't go back to query at this step.

Thanks so much!

 Index 2 consecutive rows (result) 3 consecutive rows (result) 1 1 0 1 1 0 0 0 0 1 1 1 1 1 1 1 1 1 0 0 0 1 0 0 0 0 0 1 1 1 1 1 1 1 1 1
1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Find Consecutive Rows

@v-chuncz-msft Thank you so much!!! It works.

2 REPLIES 2
Community Support Team

## Re: Find Consecutive Rows

You may add an index column in Query Editor and use DAX below to add a calculated column.

```Column =
VAR c = 2
RETURN
IF (
Table1[Index] = 0
||
VAR i = Table1[Index.1]
VAR p =
MAXX (
FILTER ( Table1, Table1[Index.1] < i && Table1[Index] = 0 ),
Table1[Index.1]
)
VAR n =
MINX (
FILTER ( Table1, Table1[Index.1] > i && Table1[Index] = 0 ),
Table1[Index.1]
)
RETURN
IF ( ISBLANK ( n ), MAX ( Table1[Index.1] ) + 1, n )
- IF ( ISBLANK ( p ), MIN ( Table1[Index.1] ) - 1, p )
- 1
< c,
0,
1
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Find Consecutive Rows

@v-chuncz-msft Thank you so much!!! It works.