Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |