# Filter in Filter

#### maring28

##### New Member
Hi all,

I've tried everything and still cannot solve this... maybe PP does not support this?? I doubt that...
My question is:
I have a table of invoices and each row is a product purchased.
I want to count the number of invoices which contain two specified type of products, however I must do this on the invoice level.
Example:
 product invoice banana 11111 apple 11111 pear 11111 cheese 11111 banana 22222 apple 22222 milk 22222 cherry 22222 banana 33333 cherry 33333 eggs 33333 bread 33333

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

I'm looking for the number of invoices which contain both bananas and apples. For this table I would want to get the answer = 2 (11111, 22222).
Is this possible? How do I do that? (In real life I have 2 tables: one with invoice&product ID and another with ProductID&ProductName).
Tried CALCULATE() with CALCULATETABLE() inside and FILTER()..No success.
Hope someone can help me...
Thanks,
Marina

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Maybe something like this?

Book1
ABCDE
2banana11111
3apple11111RijlabelsTelling van invoice
4pear11111apple2
5cheese11111111111
6banana22222222221
7apple22222banana3
8milk22222111111
9cherry22222222221
10banana33333333331
11cherry33333Eindtotaal5
12eggs33333

I didn't understand your answer... It shows the number of rows in which appears a banana or an apple.
I'm looking for a KF in Power Pivot that will do the calculation.
The goal is to count the distinct number of invoices. In this example - receive the calculated result = 2 (if I'm looking for the distinctcount of invoiced which contain both bananas and apples).
Thanks

This looks like an example of Basket Analyis:
Basket Analysis – Dax Patterns

As described on that page, you can create a measure that tells you how many orders contain any two selected products (requires you to set up your data model with a duplicate Product table with an inactive relationship to your Invoice table).

(This came up in an earlier post but I think the Basket Analysis method is the best solution )

This is awesome!!!
It works!

Thanks a lot

Replies
5
Views
2K
Replies
1
Views
668
Replies
5
Views
1K
Replies
5
Views
866
Replies
4
Views
590

1,196,346
Messages
6,014,732
Members
441,843
Latest member
benji 71

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back