Count number who have purchased both products

[metcala]

Hi

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!

[Dangar332 · Accepted solution]

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)

[Dangar332 · Accepted solution]

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.

[metcala]

Hi

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.

[Dangar332 · Accepted solution]

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)

[metcala]

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

[Dangar332 · Accepted solution]

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.

[Dangar332 · Accepted solution]

Hi, @metcala 

is above code work for you or not?

[metcala]

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

[Dangar332]

hi, @metcala 

try below

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