Forum Discussion
Consecutive Row Counter Column
Hi there! I am looking to make a counter that counts the consecutive rows and resets when there is a nonconsecutive value. Here is an example: Animal Counter Tiger 1 Tiger ...
See if this works:
Column 2 = VAR __index = CALCULATE(MAX([Index])) VAR __tmpTable1 = FILTER('Table34',[Animal]=EARLIER([Animal])&&[Index]<EARLIER([Index])) VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[Index] - MAXX(FILTER(ALL('Table34'),[Index]<EARLIER([Index]) && [Animal]=EARLIER([Animal])),[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))PBIX is attached.
I just wanted to add this link to this topic!
Related to ImkeF's solution...
https://blog.crossjoin.co.uk/2014/01/03/aggregating-by-local-groups-in-power-query/
Anyway I'll "process" :smileyvery-happy: Greg_Deckler's solution later today :smileywink:
Sean - Here it is with comments if it helps! :)
Column 2 =
VAR __index = CALCULATE(MAX([Index])) //What is my current row index?
VAR __tmpTable1 = FILTER('Table34',[Animal]=EARLIER([Animal])&&[Index]<EARLIER([Index])) //Return all rows earlier than the current row within the same "group"
VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[Index] - MAXX(FILTER(ALL('Table34'),[Index]<EARLIER([Index]) && [Animal]=EARLIER([Animal])),[Index])) //For each returned row, calculate the difference between the index values within the same group. For rows in grouped sequence, this will be 1 but for rows within a group that are out-of-sequence this value will be greater than 1
VAR __max = MAXX(__tmpTable2,[Index]) //Figure out the max index in the current table.
VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[Index]) //In order to account for "skips" in the grouping, figure out the max index value of the latest "skip" (the row right after the skip where the group starts again)
VAR __tmpTable3 = FILTER(__tmpTable2,[Index]>=__maxStart) //Filter out all the other junk because we don't want to count rows before the skip
RETURN IF(ISBLANK(__max),1,IF(__max=[Index]-1,COUNTX(__tmpTable3,[Index])+1,1)) //If __max is blank, we know that we are at the start of the table, so 1. If the max index of our original table is 1 less than the current index, we know that we are in sequence so we count all of our filtered rows (which don't include rows past a "skip"), otherwise return 1 because we know we are on the row immediately after a "skip.Also, because I was bored, here it is as a measure! With a few small improvements and a little bit better commenting.
Measure 12 = VAR __index = CALCULATE(MAX([Index])) //What is my current row index? VAR __group = CALCULATE(MAX([Animal])) //What is my current group? VAR __tmpTable1 = FILTER(ALL('Table34'),[Animal]=__group&&[Index]<__index) //Return all rows earlier than the current row within the same "group" VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[Index] - MAXX(FILTER(ALL('Table34'),[Index]<EARLIER([Index]) && [Animal]=EARLIER([Animal])),[Index])) //For each returned row, calculate the difference between the current index value and the previous index value within the same group. For rows in grouped sequence, this will be 1 but for rows within a group that are out-of-sequence this value will be greater than 1 VAR __max = MAXX(__tmpTable2,[Index]) //Figure out the max index in the current filtered table. VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[Index]) //In order to account for "skips" in the grouping, figure out the max index value of the latest "skip" (the row right after the skip where the group starts again) This will be the greatest index where the difference from the previous index in the same group is greater than 1 (previous row) VAR __tmpTable3 = FILTER(__tmpTable2,[Index]>=__maxStart) //Filter out all the other junk because we don't want to count rows before the skip RETURN IF(ISBLANK(__max),1,IF(__max=__index-1,COUNTROWS(__tmpTable3)+1,1)) //If __max is blank, we know that we are at the start of the table, so 1. If the max index of our original table is 1 less than the current index, we know that we are in sequence so we count all of our filtered rows (which don't include rows past a "skip"), otherwise return 1 because we know we are on the row immediately after a "skip.Just curious Greg_Deckler or anyone else who worked on this....have you tested it on a large data set? I haven't worked much with temp tables inside of a DAX measure and am curious how they perform. Any idea how long it would take for a table or visual to load if you had millions of rows?