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'[Ord...
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

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