TRACKING CONSECUTIVE VALUES AND ADDING

[RapidPowerBI]

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.

side	Results
1	0
2	0
1	1
1	2
2	0
1	0
2	1
2	2
2	3
2	4
1	0
2	0
1	1
1	2
1	3
2	0
1	0

cheers.

[v-frfei-msft · Accepted solution]

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

[RapidPowerBI]

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

Side	Location	Column	Column 2	Column 3	Expected
1	1
2	1
1	1
2	1
1	1
2	1
1	1	1	1	1	1
1	2	1	2	2	2
2	1	1	3	3	1
2	2	1	4	4	2
1	1		4
2	1		4
1	1		4
2	1		4
1	1		4
2	1		4
1	1	1	5	1
1	2	1	6	2
1	3	1	7	3
1	4	1	8	4
2	1		8
1	1	1	9	1
1	2	1	10	2
1	3	1	11	3
1	4	1	12	4
2	1		12
1	1	1	13	1
1	2	1	14	2
1	3	1	15	3
2	1		15
1	1		15
2	1	1	16	1	1
2	2	1	17	2	2
2	3	1	18	3	3
1	1	1	19	4	1
1	2	1	20	5	2
2	1	1	21	6	1
2	2	1	22	7	2
1	1	1	23	8	1
1	2	1	24	9	2
2	1		24
1	1	1	25	1	1
1	2	1	26	2	2
1	3	1	27	3	3
1	4	1	28	4	4
1	5	1	29	5	5
1	6	1	30	6	6
1	7	1	31	7	7
1	8	1	32	8	8
1	9	1	33	9	9
1	10	1	34	10	10
1	11	1	35	11	11