Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

IF Statement, Switch statement - Direct Query

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

Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    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.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

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

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    6 years ago

    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.