Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MI04
New Member

Searching for Multiple Substrings within a column

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: 

 

RecipeIDRecipe_Ingredients

1

Flour, Butter, Sugar
2Sugar, Eggs, Salt
3Honey, Milk, Sugar
4Tea Powder, Sugar, Honey
5Sugar, 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!

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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.

 

mahoneypat_0-1630410369784.png

 

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 = vNumSel1 )

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
mahoneypat
Employee
Employee

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.

 

mahoneypat_0-1630410369784.png

 

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 = vNumSel1 )

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


This worked perfectly, thanks a lot!

Greg_Deckler
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.