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

[KyleTaylor]

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

[CNENFRNL]

[KyleTaylor]

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

[Jihwan_Kim]

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 

[KyleTaylor]

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