IF Statement, Switch statement - Direct Query

[Anonymous]

I am trying to make a new calculated column or measure (whichever is compatible) in my report. I am running into some issues however do to the data being accessed via Direct Query.

I was initially trying to create the following custom column: 

Success/Fail = IF(SEARCH("Canceled", 'tsd v_ChangeRequestTasks'[CRQ Success]),"FAIL",

                             IF(SEARCH("Failed", 'tsd v_ChangeRequestTasks'[CRQ Success]), "FAIL", "SUCCESS"))

but received the message: "Function 'SEARCH' is not allowed as part of calculated column DAX expressions on DirectQuery models."

If the measure [CRQ Success] contains the keyword “Canceled”, or “Failed”, I would like the new custom column to say “FAIL”. Otherwise I would like the new column to say “Success” . (See pic)

I believe this is a simple DAX statement, but I have been running into an error the way I have approached the issue because I am using Direct Query for this dataset.

Example Data:

CRQ Success	NEW COLUMN
Success, Success	Success
Canceled	FAIL
Success, Canceled	FAIL
Failed, Success	FAIL

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We can use CONTAINSSTRING function to meet your requirement.

We can create a measure like this,

We can create a measure like this,
Measure = 
IF(
    CONTAINSSTRING( MAX('Table'[CRQ Success]),"Canceled"),"FAIL",
    IF(
        CONTAINSSTRING(MAX('Table'[CRQ Success]),"Failed*"), "FAIL", "SUCCESS"))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

[Greg_Deckler]

@Anonymous - You may have better luck with a measure versus a column.