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
RapidPowerBI
Frequent Visitor

TRACKING CONSECUTIVE VALUES AND ADDING

Hi there,

 

I want to keep track of the the consecutive values and add as we go . we have column side and we are after the results column, hope that makes sense.

 

sideResults
10
20
11
12
20
10
21
22
23
24
10
20
11
12
13
20
10

 

cheers.

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @RapidPowerBI ,

 

We can achieve that by creating three calculated columns.

 

Column = 
VAR a = 'Table'[Index]
VAR a_1 = 'Table'[Index] - 1
VAR ap1 = 'Table'[Index] + 1
VAR sid_1 =
    CALCULATE ( MAX ( 'Table'[side] ), FILTER ( 'Table', 'Table'[Index] = a_1 ) )
VAR sidp1 =
    CALCULATE ( MAX ( 'Table'[side] ), FILTER ( 'Table', 'Table'[Index] = ap1 ) )
RETURN
    IF (
        sid_1 <> 'Table'[side]
            && 'Table'[side] = sidp1,
        1,
        IF (
            sid_1 = 'Table'[side]
                && 'Table'[side] = sidp1,
            1,
            IF ( sidp1 <> 'Table'[side] && sid_1 = 'Table'[side], 1, BLANK () )
        )
    )
Column 2 = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[Index] <= EARLIER ( 'Table'[Index] )
            && 'Table'[Column] = 1
    )
)
Column 3 = 
VAR i = 'Table'[Index]
VAR b =
    CALCULATE (
        MAX ( 'Table'[Column 2] ),
        FILTER ( 'Table', 'Table'[Index] < i && 'Table'[Column] = BLANK () )
    )
RETURN
    IF ( 'Table'[Column] <> BLANK (), 'Table'[Column 2] - b, BLANK () )

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

2 REPLIES 2
v-frfei-msft
Community Support
Community Support

Hi @RapidPowerBI ,

 

We can achieve that by creating three calculated columns.

 

Column = 
VAR a = 'Table'[Index]
VAR a_1 = 'Table'[Index] - 1
VAR ap1 = 'Table'[Index] + 1
VAR sid_1 =
    CALCULATE ( MAX ( 'Table'[side] ), FILTER ( 'Table', 'Table'[Index] = a_1 ) )
VAR sidp1 =
    CALCULATE ( MAX ( 'Table'[side] ), FILTER ( 'Table', 'Table'[Index] = ap1 ) )
RETURN
    IF (
        sid_1 <> 'Table'[side]
            && 'Table'[side] = sidp1,
        1,
        IF (
            sid_1 = 'Table'[side]
                && 'Table'[side] = sidp1,
            1,
            IF ( sidp1 <> 'Table'[side] && sid_1 = 'Table'[side], 1, BLANK () )
        )
    )
Column 2 = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[Index] <= EARLIER ( 'Table'[Index] )
            && 'Table'[Column] = 1
    )
)
Column 3 = 
VAR i = 'Table'[Index]
VAR b =
    CALCULATE (
        MAX ( 'Table'[Column 2] ),
        FILTER ( 'Table', 'Table'[Index] < i && 'Table'[Column] = BLANK () )
    )
RETURN
    IF ( 'Table'[Column] <> BLANK (), 'Table'[Column 2] - b, BLANK () )

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi @v-frfei-msft 

 

Thanks for your help, but in case of different numbers being consecutive back to back 'column 3' ignores the change of number (side). I have added a table along with the code I have been playing around with ('Location').

 

Location = 
VAR __index = CALCULATE(MAX([Index]))
VAR __tmpTable1 = FILTER('Table',[Side]=EARLIER([Side])&&[Index]<EARLIER([Index]))
VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[Index] - MAXX(FILTER(ALL('Table'),[Index]<EARLIER([Index]) && [Side]=EARLIER([Side])),[Index]))
VAR __max = MAXX(__tmpTable2,[Index])
VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[Index])
VAR __tmpTable3 = FILTER(__tmpTable2,[Index]>=__maxStart)
RETURN IF(ISBLANK(__max),1,IF(__max=[Index]-1,COUNTX(__tmpTable3,[Index])+1,1))

 

 

SideLocationColumnColumn 2Column 3Expected
11    
21    
11    
21    
11    
21    
111111
121222
211331
221442
11 4  
21 4  
11 4  
21 4  
11 4  
21 4  
11151 
12162 
13173 
14184 
21 8  
11191 
121102 
131113 
141124 
21 12  
111131 
121142 
131153 
21 15  
11 15  
2111611
2211722
2311833
1111941
1212052
2112161
2212272
1112381
1212492
21 24  
1112511
1212622
1312733
1412844
1512955
1613066
1713177
1813288
1913399
1101341010
1111351111

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.