Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count rows with multiple criterias

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 idType
1apple
2apple
2pear
3pear
4pear
4carrot
4apple
5carrot
6carrot

 

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

Need Help

4 Replies

  • mh2587's avatar
    mh2587
    Icon for Super User rankSuper User
    4 years ago

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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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])

     

     

  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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() )