Forum Discussion
Spotting Sequences
Hello,
I have the following problem.
Suppose I am interested in finding sequences in [Value] made of 1's. I want to write the starting index of a sequence next to each of its members (column [Starting_Index]). Here is an illustration (I have columns [Index] and [Value], and want to produce column [Starting_Index]):
| Index | Value | Starting_Index |
| 1 | 0 | 0 |
| 2 | 1 | 2 |
| 3 | 1 | 2 |
| 4 | 1 | 2 |
| 5 | 0 | 0 |
| 6 | 0 | 0 |
| 7 | 1 | 7 |
| 8 | 1 | 7 |
I thought maybe I could use the Starting_Index column in its creation itself, but obviously that is not how it works.
Starting_Index = IF([Value]=0;0;IF(LOOKUPVALUE([Value];[Index];[Index]-1)<>[Value];[Index];LOOKUPVALUE([Starting_Index];[Index];[Index]-1)))
I would be very grateful if somebody could help me.
Thank you.
3 Replies
Hi klaustukas,
It's difficult to achieve your requirement using DAX because DAX can't recognize in which row 0 has become 1. I would you to use R script instead. About how to use R script in power bi, please refer to: https://docs.microsoft.com/en-us/power-bi/desktop-r-in-query-editor.
Regards,
Jimmy Tao
This calculated column might work with full data
It works with this example
Column = VAR Temp = CALCULATE ( MAX ( [Index] ), FILTER ( Table1, [Index] < EARLIER ( [Index] ) && [Value] = 0 ) ) VAR Temp1 = CALCULATE ( MIN ( [Index] ), FILTER ( Table1, [Index] > EARLIER ( [Index] ) && [Value] = 0 ) ) RETURN IF ( [Value] = 1 && [Index] > temp && OR ( [Index] < temp1, ISBLANK ( temp1 ) ), temp + 1, 0 )
