Forum Discussion

lewisgrantevans's avatar
lewisgrantevans
Regular Visitor
5 years ago
Solved

Using DAX to pick out keywords

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.

 

ServiceService 2
ASIC;CANADAYes
ASIC;CANADA;CFTCYes
EMIR;HKMANo
ASIC;CFTC;HKMA;EMIRYes
CANADA;EMIR;HKMAYes

 

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

Need Help
  • parry2k's avatar
    parry2k
    5 years ago

    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

     

     

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    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