topic Re: DAX measure to count customers with multiple sizes in each order per day in DAX Commands and Tips

DAX measure to count customers with multiple sizes in each order per day

KyleTaylor — Tue, 22 Jun 2021 05:56:03 GMT

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

Re: DAX measure to count customers with multiple sizes in each order per day

Jihwan_Kim — Tue, 22 Jun 2021 07:47:54 GMT

Customers count multiple orders : =

IF (

ISFILTERED ( Dates[Month Name] ),

COUNTROWS (

SUMMARIZE (

FILTER (

ADDCOLUMNS (

SUMMARIZE ( Data, Dates[Month Name], Customers[Customer ID], Data[Order ID] ),

"@countorders", CALCULATE ( COUNTROWS ( VALUES ( Data[Size] ) ) )

[@countorders] > 1

Customers[Customer ID]

)

https://www.dropbox.com/s/z1txb9kf7zgk1ti/kyletaylor.pbix?dl=0

Re: DAX measure to count customers with multiple sizes in each order per day

CNENFRNL — Tue, 22 Jun 2021 10:04:04 GMT

Re: DAX measure to count customers with multiple sizes in each order per day

KyleTaylor — Tue, 22 Jun 2021 22:34:11 GMT

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.

# Multi Sizes =

VAR __grp =

ADDCOLUMNS (

SUMMARIZE ( ORDERS, ORDERS[Order ID], ORDERS[Customer ID], ORDERS[Type] ),

"@count", CALCULATE( DISTINCTCOUNT( ORDERS[Size] ) )

)

RETURN

COUNTROWS ( FILTER( __grp, [@count] > 1 ) )

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

Re: DAX measure to count customers with multiple sizes in each order per day

KyleTaylor — Tue, 22 Jun 2021 22:36:24 GMT

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.

Customers count multiple orders : =

IF (

ISFILTERED ( Dates[Month Name] ),

COUNTROWS (

SUMMARIZE (

FILTER (

ADDCOLUMNS (

SUMMARIZE ( Data, Dates[Month Name], Customers[Customer ID], Data[Order ID], Data[Type] ),

"@countorders", CALCULATE ( COUNTROWS ( VALUES ( Data[Size] ) ) )

[@countorders] > 1

Customers[Customer ID]

)

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