Forum Discussion

ramhariessentia's avatar
ramhariessentia
Helper I
5 years ago
Solved

Search Query Syntax error

Hi all,

I am an occasional user of Power BI so I tend to forget fundamentals sometimes,I am trying to create "measure" using DAX Search and Switch function, Basically, I am looking at sentences created as cells for keywords.

I used following formula but its showing error w.r.t table values.

Category = SWITCH(TRUE(),
SEARCH("CANCEL",'Final'[Note-0],1,0)>0,"CANCEL",
SEARCH("DELETE",'Final'[Note-0],1,0)>0,"CANCEL",
SEARCH("REJECT",'Final'[Note-0],1,0)>0,"CANCEL",)
Final'[Note-0] is the column from which i am trying to find the strings cancel, delet and reject
Need Help
  • Icey's avatar
    Icey
    5 years ago

    Hi ramhariessentia ,

     

    Since you are creating a Measure, it is needed to use MAX, MIN, SELECTEDVALUE, etc. to specify the current row. Try this:

    Category =
SWITCH (
    TRUE (),
    SEARCH ( "CANCEL", MAX ( 'Final'[Note-0] ), 1, 0 ) > 0, "CANCEL",
    SEARCH ( "DELETE", MAX ( 'Final'[Note-0] ), 1, 0 ) > 0, "CANCEL",
    SEARCH ( "REJECT", MAX ( 'Final'[Note-0] ), 1, 0 ) > 0, "CANCEL"
)

     

    Or, you can also use CONTAINSSTRING function:

    Category 2 =
SWITCH (
    TRUE (),
    CONTAINSSTRING ( "CANCEL", MAX ( Final[Note-0] ) ), "CANCEL",
    CONTAINSSTRING ( "DELETE", MAX ( 'Final'[Note-0] ) ), "CANCEL",
    CONTAINSSTRING ( "REJECT", MAX ( 'Final'[Note-0] ) ), "CANCEL"
)

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

2 Replies

Replies have been turned off for this discussion
  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    Looks like you just had an extra comma at the end.

     

    Category =
    SWITCH (
        TRUE (),
        SEARCH (
            "CANCEL",
            'Final'[Note-0],
            1,
            0
        ) > 0, "CANCEL",
        SEARCH (
            "DELETE",
            'Final'[Note-0],
            1,
            0
        ) > 0, "CANCEL",
        SEARCH (
            "REJECT",
            'Final'[Note-0],
            1,
            0
        ) > 0, "CANCEL"
    )

     

    Pat

  • Icey's avatar
    Icey
    Community Support
    5 years ago

    Hi ramhariessentia ,

     

    Since you are creating a Measure, it is needed to use MAX, MIN, SELECTEDVALUE, etc. to specify the current row. Try this:

    Category =
SWITCH (
    TRUE (),
    SEARCH ( "CANCEL", MAX ( 'Final'[Note-0] ), 1, 0 ) > 0, "CANCEL",
    SEARCH ( "DELETE", MAX ( 'Final'[Note-0] ), 1, 0 ) > 0, "CANCEL",
    SEARCH ( "REJECT", MAX ( 'Final'[Note-0] ), 1, 0 ) > 0, "CANCEL"
)

     

    Or, you can also use CONTAINSSTRING function:

    Category 2 =
SWITCH (
    TRUE (),
    CONTAINSSTRING ( "CANCEL", MAX ( Final[Note-0] ) ), "CANCEL",
    CONTAINSSTRING ( "DELETE", MAX ( 'Final'[Note-0] ) ), "CANCEL",
    CONTAINSSTRING ( "REJECT", MAX ( 'Final'[Note-0] ) ), "CANCEL"
)

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.