Forum Discussion

chriser's avatar
chriser
Frequent Visitor
7 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       ...
Need Help
  • AlB's avatar
    AlB
    7 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
)

     

ChrisMendoza's avatar
ChrisMendoza
Resident Rockstar
7 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"