Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 ...
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    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. 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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?