locked
DAX Search for multiple strings RRS feed

  • Question

  • Hello

    I’ve got course names as follows

    “Certificate Mathematics”

    “Certificate in Science Level 1”

    “Certificate in English L1”

    I want to search for multiple strings within these strings

    If there is ‘Certificate’ and ‘L1’ or ‘Level 1’, I want to return 1 otherwise return 0

    I’ve created a calculated column

    =IF(SEARCH("Certificate*L1", [Course], 1, 0) > 0,1, 0) , which works

    But I’m not sure how to search for ‘Level’ also. I’ve tried multiple ways to include || and && but can’t get it to work, e.g.

    =IF(SEARCH("Certificate*L1") || SEARCH("Certificate*Level 1"), [Course], 1, 0) > 0,1, 0)

    Any suggestion much appreciated.

    Roy


    Thursday, January 3, 2019 12:34 PM

Answers

  • Olaf

    Many thanks for the prompt reply.

    I also fixed by using a Switch expression, I will try your suggestions to test speed

    = SWITCH (
                  TRUE (),
                  SEARCH ( "Certificate*L1", 'Table1'[Course],, 0 ) >0,1,  SEARCH ( "Certificate*Level 1", 'Table1'[Course],, 0 ) >0,1,0)

    Regards

    • Marked as answer by ryand09 Thursday, January 3, 2019 1:28 PM
    Thursday, January 3, 2019 1:28 PM

All replies

  • Hello Roy,

    3 possible Solutions:

    =IFERROR(SEARCH("Certificate*L*1*", Tabelle1[Course]), 0)

    A additional * Wildcard in search string between L and 1, but this may return more then wanted

    =IFERROR(SEARCH("Certificate*L1", Tabelle1[Course]), 0) + IFERROR(SEARCH("Certificate*Level 1", Tabelle1[Course]), 0)

    "Add" the numeric result of SEARCH; but this may return also 2 as value, depending on course Name and search string

    =IFERROR(SEARCH("Certificate*L1", Tabelle1[Course]), 0) || IFERROR(SEARCH("Certificate*Level 1", Tabelle1[Course]), 0)

    Logical OR of the search result.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, January 3, 2019 1:11 PM
  • Olaf

    Many thanks for the prompt reply.

    I also fixed by using a Switch expression, I will try your suggestions to test speed

    = SWITCH (
                  TRUE (),
                  SEARCH ( "Certificate*L1", 'Table1'[Course],, 0 ) >0,1,  SEARCH ( "Certificate*Level 1", 'Table1'[Course],, 0 ) >0,1,0)

    Regards

    • Marked as answer by ryand09 Thursday, January 3, 2019 1:28 PM
    Thursday, January 3, 2019 1:28 PM