Spotting Sequences

[klaustukas]

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.

[jthomson · Accepted solution]

The approach I'd take would be to find the last smaller index value that's zero, then add one to it - try:

startingindex = var currentindex = max(Table1[index]) var currentvalue = max(Table1[value]) var lastzero = CALCULATE(max(Table1[index]),Table1[value]=0,Table1[index]<currentindex) return if(currentvalue=0,blank(),lastzero+1)