Containsstring or any DAX to lookup keyword

[Wendy_WL]

Appreciate any help on lookup keyword using "Search" table to find whether there is any match in text table ("String" table).

If any keyword in "Search" table found in "String" table, show as "TRUE".

If there is no word in "Search" table found in "String" table, show as "FALSE".

Keyword in "Search" table can be either upper and lower case.

Both my "Search" table and "String" table can be quite long list.

I have used CONTAINSSTRING to create a new table in Power Bi but the output is not correct. 

Last 2 rows Within approved budget amount and Event to hold during peak tourist season should be "FALSE" and not "TRUE".

My code is :

Table1 =

ADDCOLUMNS(String,

    "containsString",

        CONTAINSSTRING ('String'[String], SELECTEDVALUE(('Search'[Search]))))

In addition, appreciate your assistance to count the number of rows in "Search" table found in "String" table.  Thank you very much.

POWER BI :

[Wendy_WL]

Here is link of my Power Bi file and data source:

https://drive.google.com/drive/folders/1DX0SiYNSG0C4WI1pUrwulp0N_g8O_y4S?usp=drive_link 

[Wendy_WL]

Thank you very much, Greg.  Your DAX codes work.   

Here is link of my Power Bi file and data source:

https://drive.google.com/drive/folders/1DX0SiYNSG0C4WI1pUrwulp0N_g8O_y4S?usp=drive_link 

Another question:  I add a "Site" table.  If there is any match to "Site" table, then show Result and List out the keyword found.   Appreciate if you could help me to list out keyword found as shown in blue font under "Found List".

Another Question: I follow your codes to create 2 columns "Match Site Column" and "ContainsString with Condition Column".

Would it be possible to combine these 2 columns into 1 column instead of creating 2 separate columns.

ContainsString with Condition Column

Match Site Column =

   VAR __Site = [Site]

  VAR __Table1 =

    ADDCOLUMNS(

      'Site',

      "Found", CONTAINSSTRING( __Site, [Site Code] )

    )

  VAR __Result = IF( COUNTROWS( FILTER( __Table1, [Found] = TRUE() ) ) > 0, TRUE(), FALSE() )

RETURN

  __Result

ContainsString with Condition Column =

  VAR __String = [String]

  VAR __Table =

    ADDCOLUMNS(

      'Search',

      "Found", CONTAINSSTRING( __String, [Search] )

    )

  VAR __Result = IF( COUNTROWS( FILTER( __Table, [Found] = TRUE() ) ) > 0 && [Match Site Column]=TRUE(), TRUE(), FALSE() )

RETURN

  __Result

[Greg_Deckler · Accepted solution]

@Wendy_WL Can you post sample data as text? Expected results would help as well.

Without testing, I would think adding a coumn to your String table like this would be the way to go:

ContainsString Column =
  VAR __String = [String]
  VAR __Table = 
    ADDCOLUMNS(
      'Search',
      "Found", CONTAINSSTRING( __String, [Search] )
    )
  VAR __Result = IF( COUNTROWS( FILTER( __Table, [Fouund] = TRUE() ) ) > 0, TRUE(), FALSE() )
RETURN
  __Result