Count rows with multiple criterias

[Anonymous]

Hello everyone,

I'm looking for help on something that looks easy but I can't wrap my head around.

I have a table with 2 columns with consumer ids and the type of product they bought, as shown on the following table.

Consumer id	Type
1	apple
2	apple
2	pear
3	pear
4	pear
4	carrot
4	apple
5	carrot
6	carrot

I'm looking for a Dax formula to quickly retrive the count of consumers who have bought both an apple & a pear, so in this case only id 2 & 4.

Thanks for your help

[CNENFRNL · Accepted solution]

Generic solution

[tex628]

Try this:

Count = 
VAR Id_ = 'Table'[Consumer id]
return
IF( CALCULATE( COUNTROWS('Table') ,'Table'[Consumer id] = Id_ ,'Table'[Type] = "Apple" || 'Table'[Type] = "Pear" ) = 2 , 1 , BLANK() )

[amitchandak]

@Anonymous , You can try solution by @mh2587 , But I think it should be like below if you need both

countx(filter(addcolumns(summarize(Table, Table[Consumer id]) , "_apple", countx(filter(Table, Table[Type] ="apple"),[Consumer id] )
, "_pear", countx(filter(Table, Table[Type] ="pear"),[Consumer id] ) ), not(isblank(_apple)) && not(isblank(_pear)) ),[Consumer id])

[mh2587]

=Calculate(COUNT[Consumer ID],[Type] IN {"apple","pear"})