Filter Visual works as 'does not contain'

[aidenblake]

I have a following data:

ReceipeKey Receipe Ingredients

1	Pancake	Flour, Baking Soda, Sugar, Salt, Milk, Butter, Egg
2	Omelettes	Flour, Salt, Milk, Egg
3	Bread	Flour, Salt, Yeast, Water, Oil
4	Bun	Flour, Sugar, Yeast, Milk, Butter, Salt, Egg

The goal is to filter for a single ingredient, so I have created a reference table for this purpose (which is connected to the previous table through the column 'Ingredients'):

Ingredients Ingredient Filter

Flour, Baking Soda, Sugar, Salt, Milk, Butter, Egg	Flour
Flour, Baking Soda, Sugar, Salt, Milk, Butter, Egg	Baking Soda
Flour, Baking Soda, Sugar, Salt, Milk, Butter, Egg	Sugar
Flour, Baking Soda, Sugar, Salt, Milk, Butter, Egg	Salt
Flour, Baking Soda, Sugar, Salt, Milk, Butter, Egg	Milk
Flour, Baking Soda, Sugar, Salt, Milk, Butter, Egg	Butter
Flour, Baking Soda, Sugar, Salt, Milk, Butter, Egg	Egg
Flour, Salt, Milk, Egg	Flour
Flour, Salt, Milk, Egg	Salt
Flour, Salt, Milk, Egg	Milk
Flour, Salt, Milk, Egg	Egg
Flour, Salt, Yeast, Water, Oil	Flour
etc..	etc..

This is working great so far if you want to filter for specific ingredients that you'd like in the recipe, such as Egg and Salt.

Problem: It doesn't work if you don't want a particular ingredient in the recipe. For example, if a user selects all ingredients except Milk (due to lactose intolerance), recipes with milk still appear because of other selected ingredients.

Question: Is there a way to ensure that only recipes without Milk are shown when Milk is not selected? I need this as a filtering option for users.

I would like to use it for a multi-page report, so using a measure as a filter for each visual is not the most optimal solution. Ideally, a dropdown filter would be the best option.

Thank you so much for your help.

[OwenAuger]

Hi @aidenblake 

This is an interesting one 🙂

Before suggesting how to handle this in the model, I just wanted to clarify the requirements.

If mutiple Ingredients are not selected, should all Recipes that include at least one of those Ingredients be excluded?

1. For example, using the example data, if both Oil and Sugar are not selected, then Pankcake, Bread and Bun should be excluded, leaving just Omelettes visible?

2. And in the simpler case of just excluding Milk, only Bread would be visible?