Forum Discussion

chriser's avatar
chriser
Frequent Visitor
6 years ago
Solved

Count consecutive values with filter

Hi, 

 

I would like to count consecutive values with a filter as per below example.

  • OrderNumber is unique
  • For not registered users, return 0

 

CustomerID     OrderNumber     Registered                    Result

1                      A                          Yes                               1

2                      B                          No                                0

3                      C                          Yes                               1

1                      D                          Yes                               2

2                      E                           No                               0

1                      F                           Yes                               3

3                      G                          Yes                               2

 

 

Thanks in advance

Need Help
  • AlB's avatar
    AlB
    6 years ago

    Hi chriser 

    Try this for a calculated column:

     

    Result =
IF (
    Table1[Registered] = "Yes",
    CALCULATE (
        COUNT ( Table1[CustomerID] ),
        ALLEXCEPT ( Table1, Table1[CustomerID] ),
        Table1[OrderNumber] <= EARLIER ( Table1[OrderNumber] )
    ),
    0
)

     

3 Replies

  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    Hi chriser 

    Try this for a calculated column:

     

    Result =
IF (
    Table1[Registered] = "Yes",
    CALCULATE (
        COUNT ( Table1[CustomerID] ),
        ALLEXCEPT ( Table1, Table1[CustomerID] ),
        Table1[OrderNumber] <= EARLIER ( Table1[OrderNumber] )
    ),
    0
)

     

    • chriser's avatar
      chriser
      Frequent Visitor
      6 years ago

      Thanks AlB! Works really well, although this approach is memory hungry. My poor Surface failed to deliver results on over 1m rows, but after filtering table to 8k rows it worked well. Thanks a lot!

  • ChrisMendoza's avatar
    ChrisMendoza
    Resident Rockstar
    6 years ago

    chriser -

    Power Query as a possible solution:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4sjUYqVYnWglIyDbCYj98sFcYyDTGUkapNwFTbkrQjlI2g1JGqTdHcaPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CustomerID = _t, OrderNumber = _t, Registered = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CustomerID", Int64.Type}, {"OrderNumber", type text}, {"Registered", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CustomerID"}, {{"Count", each _, type table [CustomerID=number, OrderNumber=text, Registered=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Trans", 1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"OrderNumber", "Registered", "Trans"}, {"OrderNumber", "Registered", "Trans"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Result", each if [Registered] = "Yes" then [Trans] else 0),
    #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"OrderNumber", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Count", "Trans"})
in
    #"Removed Columns"