Using DAX to pick out keywords

[lewisgrantevans]

Hello,

I know some basic DAX but I have never found a reasonable way to extract keywords from the results in the column, as I can do very easily in Splunk.

I would like to create a new column called 'Service 2', and I would like to scan the results in 'Service' for the rows that include 'ASIC', 'CANADA' or 'CFTC'. If the data includes one or more of the 3 jurisdictions, I would like to mark it as 'Yes' in the new column 'Service 2', and 'No' if it does not include any of these 3.

Service	Service 2
ASIC;CANADA	Yes
ASIC;CANADA;CFTC	Yes
EMIR;HKMA	No
ASIC;CFTC;HKMA;EMIR	Yes
CANADA;EMIR;HKMA	Yes

Is it possible to do this in DAX? So far I have only been able to do this if i put the exact results in the arguement, for example -

Service 2 = IF([Service]="CANADA;EMIR;HKMA", "Yes", "No"

But in this instance I have way too many combinations to include them. In Splunk query language I would just put * at the beginning and end of the word and it would scan the resuts for those keywords.

Any help is much appreciated!

Thanks,

Lewis

[parry2k · Accepted solution]

@lewisgrantevans try this by adding

Service 2 = 
IF ( 
CONTAINSSTRING( Table[Service], "CANADA"  ) ||
CONTAINSSTRING ( Table[Service], "EMIR" ) ||
CONTAINSSTRING ( Table[Service], "HKMA" )
, "Yes", "No"
)

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

new columns

[lewisgrantevans]

Perfect thank you! CONTAINSSTRING ftw!