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.

[v-yuta-msft]

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

[Zubair_Muhammad]

@klaustukas@v-yuta-msft

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
    )

[Zubair_Muhammad · Accepted solution]

@klaustukas

File attached as well