Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Amymai
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!

 

Index2 consecutive rows (result)3 consecutive rows (result)
110
110
000
111
111
111
000
100
000
111
111
111
1 ACCEPTED SOLUTION

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

 

View solution in original post

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@Amymai,

 

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.

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.