Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
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 )
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |