Forum Discussion
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.
| 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.
Hi Anonymous ,
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 () )
2 Replies
Hi Anonymous ,
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 () )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