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
)

     

AlB's avatar
AlB
Community Champion
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
)

 

chriser's avatar
chriser
Frequent Visitor
7 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!