Finding substring with multiple values

[Anonymous]

Similar problems have been answered before but the solutions are not quite working in my case. I'm relatively new to DAX, so not sure if this is easily solvable.

My main data source is quite large, but essentially looks like the below: 

My objective is to search the highlighted "Ingredient" column for a list of substrings (example below), therefore flagging anything that has the word "tea","milk", or "strawberry"

The challenge: the table 2 contains 100+ ingredients to find, so using a variable or formula is not feasible. 

Is there a way to search a column for a larger list of substrings?

Hoping to get this output:

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please use the following DAX formula to create a calculated column:

Column =IF(
      SUMX(MatchList,
           FIND(
                UPPER(MatchList[Keyword]),
                UPPER(Companies[Company])
                ,,0
               )
          ) > 0,
      “YES!”,
      “Probably Not”
     )

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KNP]

Do you have the complete list of words somewhere?

I'd be referencing that and using a PowerQuery formula to flag the rows.

That way you have a dynamic solution simple by adding new values to the list.

[itsme]

Yes! This would be the way to go for long term solution, especially if that list changes. All you'd need to do is update that list somewhere (e.g. Excel in SharePoint...), bring that list into PQ, and reference it in a PQ formula similar to the IN operator in DAX.

[itsme]

You can create a Flag column by doing something like this:

Flag =

SWITCH (
    TRUE (),
    CONTAINSSTRING ( 'Table'[Ingredient], "tea" ), "Flag",
    CONTAINSSTRING ( 'Table'[Ingredient], "milk" ), "Flag",
    BLANK ()
)

[Anonymous]

thank you! 
I was thinking about doing an additional column like that but am trying to avoid entering the words to search manually, since there are 100+ of them in the actual dataset. is there a way to automate the creation of that formula though with the larger list of words to search for? 

[itsme]

If the strings of words you are searching for covers the entire value (like if you search for "milk" it will not pick up "whole milk", only "milk"), then you can use the IN operator and you can just list out the values, e.g. ...'Table'[Ingredient] IN { "milk", "tea", "whole milk" }... But you'd still have to enter them manually.

In the solution I gave earlier, if you have the list of words you want to filter, you can paste them into Excel and use formulas to create an entire DAX statement and then copy/paste it into Power BI. 

Still take some time but not nearly as long as typing everything down. You can do a lot of copy/paste/dragging to duplicate values in Excel.