Search Query Syntax error

[ramhariessentia]

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

[Icey · Accepted solution]

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.

[mahoneypat]

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