Searching for Multiple Substrings within a column

[MI04]

Hi everyone, 

I'm new to Power BI and I had a query regarding how to go about searching for multiple substrings within a string. I have a table of recipes where the columns are RecipeID which is just a numeric value, and Recipe_Ingredients which is a set of ingredients as shown below: 

RecipeID	Recipe_Ingredients
1	Flour, Butter, Sugar
2	Sugar, Eggs, Salt
3	Honey, Milk, Sugar
4	Tea Powder, Sugar, Honey
5	Sugar, Flour, Salt

I also have table of individual ingredients which I have set up as a Slicer: 

Ingredients

Salt

Sugar

Honey

What I would like to acheive is that f I select multiple ingredients in the slicer, it displays only those recipes which have ALL the selected ingredients. So for example, if I select Sugar and Honey, all the recipes shows must have BOTH ingredients in them. 

These examples are representative and the actual data set would have over 100 ingredients that can be chosen, and no matter how many I choose, the resulting recipes should have all the ingredients as a part of it.

Would this be possible? Thanks in advance!

[mahoneypat · Accepted solution]

Here is one way to do it with no relationship between your two tables.  You can use this measure in the table visual or use it as a visual level filter with a condition of = 1.

ShowRecipe =
VAR vSelIngredients =
    DISTINCT ( Ingredients[Ingredients] )
VAR vNumSel =
    COUNTROWS ( vSelIngredients )
VAR vThisRecipe =
    MIN ( Recipes[Recipe_Ingredients] )
VAR vNumFound =
    COUNTROWS (
        FILTER (
            vSelIngredients,
            SEARCH ( Ingredients[Ingredients], vThisRecipe,, 0 ) > 0
        )
    )
RETURN
    IF ( vNumFound = vNumSel, 1 )

Pat

[MI04]

This worked perfectly, thanks a lot!

[Greg_Deckler]

@MI04 Your best bet is to split your ingredient column by comma in PQ and then unpivot the columns. Then all you need is an AND slicer: https://community.powerbi.com/t5/Quick-Measures-Gallery/Patient-Cohort-AND-Slicer/m-p/391883#M130