Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.