Filtering over multiple rows for a single entity.

[simonb2013]

My Table : 
Box1 ; Bananas
Box1; Apples
Box1; Mangos
Box2; Bananas
Box2; Oranges
Box2; Plums
Box2; Apricots

User would like to filter and find which box has both Bananas and Plums in it.
I have over 500,000 boxes, some with hundreds of products in them.

Could someone nudge me where to start trying to solve this one ?

Many Thanks

[JordyWegman]

Hi Simon,

Do you have 2 columns?

Then make two filter panes:

1. Box
2. Fruit

Let your user select one type of fruit and in the other selection box, all your boxes appear that have the type of fruit selected. You can then also make a table with extra information about the boxes.

Jordy

Climber

[simonb2013]

That works great for one fruit.

But I want only boxes than contain BOTH : Bananas AND Plums.

If I filter on both Bananas and Plums naturally .. both boxes show.  It is doing an 'OR', where I want an 'AND'

[simonb2013]

Ok, so I have progressed and might be close to a solution.
If I select 2 number Products, then I can use this expression as a measure:

COUNT({<Box = {"=COUNT(PRODUCT)=2"}>} PRODUCT)

Now all I need to do, is to make the filter dynamic.
So, to replace the literal '2' with "GetPossibleCount(PRODUCT)"

Unfortunately I am getting woefully confused how to substitute that.
If anyone could help with the correct syntax and/or explanation that would get the below corrected ?

COUNT({<Box = {"=COUNT(PRODUCT)=$(GetPossibleCount(PRODUCT))"}>} PRODUCT)

[simonb2013 · Accepted solution]

Make a variable : vProductsAvailable = GetPossibleCount(Product)

Then use variable in expression thus : 

=Sum({<[Box] = {"=COUNT([Product])=$(vProductsAvailable)"} >} 1)