cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
klaustukas
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]):

 

IndexValueStarting_Index
100
212
312
412
500
600
717
817

 

 

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

@klaustukas

 

File attached as well

 

SpottingSequence.png


Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

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

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

Regards
Zubair

Please try my custom visuals

@klaustukas

 

File attached as well

 

SpottingSequence.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

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

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

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

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

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

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors