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,
Please help! I need a DAX measure to count the numbe of customers per order per day with multiple sizes in the order?
With the sample below, I would except the following:
Jan with 1 customer with multiple sizes in the order
Feb with 2 customers with multiple sizes in the order (Customer 222 is ignored because only 1 size in order)
I have tried COUNTROWS( Sales) and
COUNTROWS( DISTINCT( Sales[Size])) and
COUNTROWS (
EXCEPT (
VALUES ( Sales[Customer ID] ),
SUMMARIZE (
GENERATE (
VALUES ( Sales[CustomerID] ),
EXCEPT (
VALUES ( Sales[Size] ),
CALCULATETABLE ( VALUES ( Sales[Size] ) )
)
),
Sales[Customer ID]
)
)
)
Any ideas?
Date | Customer ID | Order ID | Size |
1-Jan | 123 | ABC-100 | 10 |
1-Jan | 123 | ABC-100 | 12 |
3-Feb | 123 | ABC-118 | 12 |
3-Feb | 123 | ABC-118 | 14 |
7-Feb | 222 | ABC-226 | 10 |
9-Feb | 125 | ABC-234 | 8 |
10-Feb | 125 | ABC-234 | 10 |
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thanks, that worked as expected! What if I want to add another field to the table/measure? When I add a field after Customer ID the table goes blank? Same with [Size], could that be added to the table? It goes blank when I do.
I have a field called TYPE that I want to include. The result would be:
YYYYMM | Type | # Multi Sizes |
2021-01 | Dress | 1 |
2021-02 | Dress | 1 |
2021-02 | Top | 1 |
Data:
Date | Customer ID | Order ID | Size | Type |
1-Jan | 123 | ABC-100 | 10 | Dress |
1-Jan | 123 | ABC-100 | 12 | Dress |
3-Feb | 123 | ABC-118 | 12 | Dress |
3-Feb | 123 | ABC-118 | 14 | Dress |
7-Feb | 222 | ABC-226 | 10 | Pants |
9-Feb | 125 | ABC-234 | 8 | Top |
10-Feb | 125 | ABC-234 | 10 | Top |
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks, that worked as expected! I notice there is no total on this measure? Is there a way to add a total (3 in this case)?
What if I want to add another field to the table/measure? When I add a field after Customer ID the table goes blank? Same with [Size], could that be added to the table? It goes blank when I do.
I have a field called TYPE that I want to include. The result would be:
YYYYMM | Type | # Multi Sizes |
2021-01 | Dress | 1 |
2021-02 | Dress | 1 |
2021-02 | Top | 1 |
Data:
Date | Customer ID | Order ID | Size | Type |
1-Jan | 123 | ABC-100 | 10 | Dress |
1-Jan | 123 | ABC-100 | 12 | Dress |
3-Feb | 123 | ABC-118 | 12 | Dress |
3-Feb | 123 | ABC-118 | 14 | Dress |
7-Feb | 222 | ABC-226 | 10 | Pants |
9-Feb | 125 | ABC-234 | 8 | Top |
10-Feb | 125 | ABC-234 | 10 | Top |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |