cancel
Showing results for
Did you mean:
Frequent Visitor

## 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.

1 ACCEPTED SOLUTION
Super User

@klaustukas

File attached as well

Regards
Zubair

3 REPLIES 3
Community Support

Hi

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

Super User

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
)```

Regards
Zubair

Super User

@klaustukas

File attached as well

Regards
Zubair

Announcements

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

#### Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!