Forum Discussion

joerykeizer's avatar
joerykeizer
Helper II
7 years ago
Solved

Cumulative distinct count

Hi all,

 

 I have a table with order, date and customer and I would like to count the number of cumulative orders per customer. 

 

Currently I have:

Measure = CALCULATE(DISTINCTCOUNT('Table'[Order]),FILTER(ALLSELECTED('Table'),'Table'[Order]<max('Table'[Order]))).

 

CDC Example

 

 

This works but I would like to have it counted per customer without having to filter them. (Expected results: 0-1-2-2-0-1-0-1). In addition, I'd like to know how to do the same thing with a calculated column, since using the formula as is returns only 1 number.

 

Thx,

Joery

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi joerykeizer

     

    You may try to create a measure or column as below:

    Measure =
CALCULATE (
    DISTINCTCOUNT ( Table1[Order] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[Customer] ),
        Table1[Order] < MAX ( Table1[Order] )
    )
)
    + 0

    Column =
CALCULATE (
    DISTINCTCOUNT ( Table1[Order] ),
    FILTER (
        Table1,
        Table1[Customer] = EARLIER ( Table1[Customer] )
            && Table1[Order] < EARLIER ( Table1[Order] )
    )
)
    + 0

    Regards,

    Cherie

3 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi joerykeizer

     

    You may try to create a measure or column as below:

    Measure =
CALCULATE (
    DISTINCTCOUNT ( Table1[Order] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[Customer] ),
        Table1[Order] < MAX ( Table1[Order] )
    )
)
    + 0

    Column =
CALCULATE (
    DISTINCTCOUNT ( Table1[Order] ),
    FILTER (
        Table1,
        Table1[Customer] = EARLIER ( Table1[Customer] )
            && Table1[Order] < EARLIER ( Table1[Order] )
    )
)
    + 0

    Regards,

    Cherie

    • joerykeizer's avatar
      joerykeizer
      Helper II
      7 years ago

      Hi v-cherch-msft

       

      Thank you! There is one issue though.

       

      This works perfectly in my test-dataset but calculating the column is too heavy in the actual one. Dataset is currently ~155k rows. Is there any way to do this more efficiently?

       

      Regards,

      Joery

      • v-cherch-msft's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        Hi joerykeizer

         

        You may also try to make a query with M language in Advanced Editor in Query Editor. It seems a more complicated way.

         

        Regards,

        Cherie