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

Thanks Felix for your reply!

 

This unfortunately must be done with a calculated column since the "Animal" column does not exist in the query. 

ImkeF's avatar
ImkeF
Community Champion
7 years ago

Hi MFelix,

setting the 4th (optional) parameter in the Table.Group-function to "GroupKind.Local" will do some magic in this use case ;-)

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCslMTy1SitUhnuWTmZ+Hg4FNuWtOakFGYl4JAQ4eO2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Animal = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Animal", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Animal"}, {{"Partition", each Table.AddIndexColumn(_, "Counter", 1,1), type table}}, GroupKind.Local),
    #"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"Counter"}, {"Counter"})
in
    #"Expanded Partition"

Anonymous: May I ask what your source-data exactly looks like?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    ImkeFMFelixThank you so much for replying!!!

     

    This dataset is much more hairy than what I posted as an example and cannot begin to share the source data :/ . I require a DAX solution unfortunately.

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      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.