look for specific text from entries in another table

[carlbh]

I have a table (CaseData) with a column that contains a complex and variable text string, but within this string is potentially a key standard "phrase" from a list of "string" in another table (Strings). 

e.g. text to be searched

[INFORMATIONAL 20230222-OfficeActivity-SharePointFileOperation via previously unseen IPs

Case Data

Microsoft 365 Defender has detected a security threat Review this incident. [Microsoft 365] Microsoft 365 Defender has detected a security threat in your environment View incident details:

Defender ATP Endpoint Device Isolation. Endpoint has been Isolated from the Network.

Defender ATP Endpoint Device Isolation. Endpoint has been Isolated from the Network.

Defender ATP Endpoint Device Isolation. Endpoint has been Isolated from the Network.

High severity alert: Suspicious URL clicked on Alert notification email. [Microsoft 365] Suspicious URL clicked was detected by Microsoft Defender for Endpoint on

High severity alert: Suspicious URL opened in web browser Alert notification email. [Microsoft 365] Suspicious URL opened in web browser was detected by Microso

High severity alert: Suspicious URL opened in web browser Alert notification email. [Microsoft 365] Suspicious URL opened in web browser was detected by Microso

High severity alert: Suspicious URL clicked on Alert notification email. [Microsoft 365] Suspicious URL clicked was detected by Microsoft Defender for Endpoint on

Microsoft 365 Defender has detected a security threat Review this incident. [Microsoft 365] Microsoft 365 Defender has detected a security threat in your environment View incident details:

LOW (P3)20230407-AzureADIP-Atypical travel- At 15:54 on 07/04/2023 an alert "AzureADIP - Atypical travel" has been generated with the following i

INFORMATIONAL (P4)20230314-OfficeActivity-SharePointFileOperation via previously unseen IPs- At 06:14 on 14/0

The text string I am looking for would be 

SharepointFileOperation

String	String Group
Defender has detected a security threat	Security threat
Endpoint Device Isolation.	Device Isolated
Suspicious URL clicked on	Suspicious URL
Suspicious URL opened	Suspicious URL
Atypical travel	Location anomaly
SharePointFileOperation	Sharepoint

If found I would want to return is the second column in the Strings table, "StringGroup" associated with the text string I was looking for. 

[amustafa · Accepted solution]

Create a measure column in your CaseData table as:

String Group Search =

VAR CurrentText = SELECTEDVALUE(CaseData[Case Data])

VAR Result =

    CALCULATE(

        MAX(SharepointFileOperation[String Group]),  // Using StringGroup column here

        FILTER(

            SharepointFileOperation,

            NOT ISBLANK(SEARCH(SharepointFileOperation[String], CurrentText, 1, BLANK()))

        )

    )

RETURN

    IF(ISBLANK(Result), "Not Found", Result)