Count of one column based on distinct count of another column being greater than 1

[STEVE_WT]

Hi

This is probably a basic query but i'm having no luck. 

I need a measure to count the number of accounts where the sum of distinct count of products they buy is greater than 1. Distinct count as the customer could buy the same product various times within the date range they select. Basically i need to display the number of customers who buy more than product.

So the results of the below would be 2. Customers 005 is in there twice buy just bought the same product in two different orders.

DATA TABLE
ACCOUNT NUMBER	PRODUCT
001	A
001	B
002	A
002	B
002	C
003	A
004	A
005	A
005	A

Appreciate any help.

Thanks

[bhanu_gautam · Accepted solution]

@STEVE_WT Create a measure to count distinct products per account:

DAX
DistinctProductsPerAccount =
CALCULATE(
DISTINCTCOUNT('Table'[PRODUCT]),
ALLEXCEPT('Table', 'Table'[ACCOUNT NUMBER])
)

Create a measure to count accounts with more than 1 distinct product:

DAX
AccountsWithMoreThanOneProduct =
CALCULATE(
DISTINCTCOUNT('Table'[ACCOUNT NUMBER]),
FILTER(
VALUES('Table'[ACCOUNT NUMBER]),
CALCULATE(DISTINCTCOUNT('Table'[PRODUCT])) > 1
)
)

[STEVE_WT]

Perfect. Thanks for the fast response. Much appreciated.