Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Finding substring with multiple values

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 larg...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

itsme's avatar
itsme
Resolver I
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      itsme
      Resolver I
      4 years ago

      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.