Count # of customers that bought both apples and pears

[alex1alex]

Hello!
These seems deceptively simple but I can't find any example online that answer this (or am searching for the wrong thing)
Any help would be great.

Considering this table:

Customer	Fruit
Anne	Apple
Anne	Pear
Bob	Apple
Cindy	Pear

<tbody>
</tbody>

How do I ask, "how many customers bought both apples and pears?" (which would be 1, Anne)

( If I say, Calculate(Distinctcount('table'[Customer]),table[Fruit]="Apple" || table[Fruit]="Pear])) then I get 3...which is wrong )

Kindest regards,
Alex

 

[Comfy]

Hi Alex,

I know nothing of DAX but I notice that you are using || which I believe is an OR operator.

Try using && which is the AND operator.

 

[alex1alex]

Hi Comfy, using that, it does the comparrison for the fruit on the same row. (which, is always false). Breaking it down some more, I guess I need to get a list of customers that are equal to apple and another list where they are equal to pear...and then do an intersection of those two lists....but in such away i can still use the table in pivots (which the other columns)

 

[citizenbh]

Try this:
1. Make a measure

Test:=IF(Calculate(COUNTA([Customer]);'Table'[Fruit]="Apple" || 'Table'[Fruit]="Pear")=2;1;0)

2. Create a Pivot Table with rows : Costumer

3. Apply filter on column :
Value Filters/Greater Then for measure Test>0
Or Value Filters/Equals for measure Test=1

 

[alex1alex]

Hi citizenbh,
No....
if you add Bob | Apple
as another line, then you still get a 1 with your formula....it's counting the two Apple lines, getting 2 (and therefore setting to 1)
(and sorta the opposite if you add another Anne | Apple....then you get a 3....you could sorta fix that by saying >2 (instead of =2) but the above still makes it wrong)
Thanks for trying though.
-Alex

 

[BQardi]

Also knowing nothing about DAX, but my logic says that the AND operator should be used, as stated by Comfy, something like:

Calculate(Distinctcount('table'[Customer]),table[Fruit]="Apple") && Distinctcount('table'[Customer]),table[Fruit]="Pear"]))

I don't know the correct syntax, maybe:

Calculate(Distinctcount('table'[Customer]),table[Fruit]="Apple"  && 'table'[Customer]),table[Fruit]="Pear"]))

I think you get my idea. Again I know nothing about DAX, this is just me, putting my logic out there, and hopefully can inspire/help you...

 

[Ozeroth]

Hi Alex,

Have a look at this:
Basket Analysis – DAX Patterns

Applying this pattern in your situation would mean having a Fruit table plus a duplicate Fruit table.

 

[citizenbh]

Before remove duplicate rows

 

[alex1alex]

Hi,
I looked at the basket analysis (Thanks!) but I'm not quite getting it....or more to the point....I couldn't figure out how to do that plus integrate it with the rest of the thing I'm working on. I sort of need the Basket Analysis combined with the new-and-returning-customers/ pattern.

I actually found your answer here a bit more useful.

In the end I sort of gave up and moved most of the work to PowerQuery where I GroupBy the data into a couple of tables....and then use the new and returning customers patter in DAX.

(I'm interested in seeing customers which are doing purchases both by "phone" and by "ecommerce" and how they are changing over time....)

Thank very much for the suggestion (and your other answer)!

Hi Alex,

Have a look at this:
Basket Analysis – DAX Patterns

Applying this pattern in your situation would mean having a Fruit table plus a duplicate Fruit table.