Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
76 | |
63 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
64 | |
59 | |
56 |