Filter in Filter

[maring28]

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

 

[Oeldere]

Maybe something like this?

Book1
	A	B	C	D	E
2	banana	11111
3	apple	11111		Rijlabels	Telling van invoice
4	pear	11111		apple	2
5	cheese	11111		11111	1
6	banana	22222		22222	1
7	apple	22222		banana	3
8	milk	22222		11111	1
9	cherry	22222		22222	1
10	banana	33333		33333	1
11	cherry	33333		Eindtotaal	5
12	eggs	33333
13	bread	33333
Blad12

 

[maring28]

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

 

[Ozeroth]

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 )

 

[maring28]

This is awesome!!!
It works!

Thanks a lot