Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]))).
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
Solved! Go to Solution.
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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |