topic Re: Count number who have purchased both products in DAX Commands and Tips

Count number who have purchased both products

metcala — Fri, 27 Oct 2023 15:10:24 GMT

Sorry for the simple question....

Customer	Product
A	1
A	2
B	1
C	1
D	1
D	2

I'm looking to count how many customers have purchased 1 and 2 so in the above example I'd expect 2 (A and D).

I've currently got the following but doesn't seem to be working:

=CALCULATE(DISTINCTCOUNT('table'[Customer]),FILTER('table',[Product]="1"&&[Product]="2"))

Any help would be much appreciated!

Re: Count number who have purchased both products

Dangar332 — Fri, 27 Oct 2023 15:56:38 GMT

hi, metcala

try below

COUNTX(FILTER('count',COUNT('count'[Product])>1),'count'[Customer])

Re: Count number who have purchased both products

metcala — Fri, 27 Oct 2023 15:59:39 GMT

Thanks for the response.

I don't think I was clear in my original post. There are other products but I am only after a measure that counts specifically product 1 and product 2.

Re: Count number who have purchased both products

Dangar332 — Fri, 27 Oct 2023 16:08:52 GMT

Hi, metcala

Measure 2 = var a = CALCULATE(MIN('count'[Customer]),'count'[Product]=1) var b = CALCULATE(MIN('count'[Customer]),'count'[Product]=2) return CALCULATE(COUNT('count'[Customer]),'count'[Customer]=a && 'count'[Customer]=b)

Re: Count number who have purchased both products

metcala — Mon, 30 Oct 2023 09:35:47 GMT

Thanks for your help with this one. I have just tried adding to my dataset and the measure keeps returning 2 on the data card rather than a count of the number of applications

It is working correctly when added to a matrix for Customer ID and Measure but I just need a card with the number of customers with both products.

e.g. with the following dataset I'd expect the card to display 4

Customer	Product
A	1
A	2
B	1
C	1
D	1
D	2
E	1
E	2
F	2
G	1
G	2

Re: Count number who have purchased both products

Dangar332 — Mon, 30 Oct 2023 10:21:07 GMT

Hi, metcala

try below code

result = var a = ADDCOLUMNS( ALL('count'[Customer]), "for 1", CALCULATE( MIN('count'[Customer]), 'count'[Product]=1 ), "for 2", CALCULATE( MIN('count'[Customer]), 'count'[Product]=2 ) ) return COUNTX( FILTER(a, NOT([for 1]="")&& NOT([for 2]="") ), 'count'[Customer] )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. and don't forget to give kudos.

Re: Count number who have purchased both products

Dangar332 — Tue, 31 Oct 2023 20:57:00 GMT

Hi, metcala

is above code work for you or not?

Re: Count number who have purchased both products

metcala — Wed, 01 Nov 2023 08:37:21 GMT

Sorry for late response, that's perfect! Thank you!!