Substitute multiple with DirectQuery

[UserInterface]

How can I substitute text for multiple values?

I tried to nest it as per another post here but I can't do that due to direct query.

Custom Status = SUBSTITUTE(SUBSTITUTE('Custom - Requests ++'[Request Status],"On Hold - Pending Customer Feedback","[P] Cust Feedback"),SUBSTITUTE('Custom - Requests ++'[Request Status],"On Hold - Pending 3rd Party Supplier","[P] 3rd Party"),SUBSTITUTE('Custom - Requests ++'[Request Status],"On Hold - Pending Site Visit","[P] Site Visit"),SUBSTITUTE('Custom - Requests ++'[Request Status],	"On Hold - Pending Known Problem","[P] Known Problem"))

Error i get is 'Function 'Substitute is not allowed as part of a calculated column DAX expressions on DirectQuery models'

[Seward12533 · Accepted solution]

Custom Status =
SUBSTITUTE (
    SUBSTITUTE (
        'Custom - Requests ++'[Request Status],
        "On Hold - Pending Customer Feedback",
        "[P] Cust Feedback"
    ),
    SUBSTITUTE (
        'Custom - Requests ++'[Request Status],
        "On Hold - Pending 3rd Party Supplier",
        "[P] 3rd Party"
    ),
    SUBSTITUTE (
        'Custom - Requests ++'[Request Status],
        "On Hold - Pending Site Visit",
        "[P] Site Visit"
    ),
    SUBSTITUTE (
        'Custom - Requests ++'[Request Status],
        "On Hold - Pending Known Problem",
        "[P] Known Problem"
    )
)

That nested substitute is making my head hurt even after I reformatted it.  If you can do a logical test on a value in your reqeust status column you may be able to use a caluclated column with New Status = SWITCH(TRUE(), test1, restult1, switch2, restult2, ..., elseresult) to build a custom text column. You shoudl be able to use Search, Find for text comparison and any compbination of other test to work out logic ot get what you want. 

If you can give us a better idea of what your data looks like and the desired results may be able to help with more specifics. 

[Seward12533 · Accepted solution]

Custom Status =
SUBSTITUTE (
    SUBSTITUTE (
        'Custom - Requests ++'[Request Status],
        "On Hold - Pending Customer Feedback",
        "[P] Cust Feedback"
    ),
    SUBSTITUTE (
        'Custom - Requests ++'[Request Status],
        "On Hold - Pending 3rd Party Supplier",
        "[P] 3rd Party"
    ),
    SUBSTITUTE (
        'Custom - Requests ++'[Request Status],
        "On Hold - Pending Site Visit",
        "[P] Site Visit"
    ),
    SUBSTITUTE (
        'Custom - Requests ++'[Request Status],
        "On Hold - Pending Known Problem",
        "[P] Known Problem"
    )
)

That nested substitute is making my head hurt even after I reformatted it.  If you can do a logical test on a value in your reqeust status column you may be able to use a caluclated column with New Status = SWITCH(TRUE(), test1, restult1, switch2, restult2, ..., elseresult) to build a custom text column. You shoudl be able to use Search, Find for text comparison and any compbination of other test to work out logic ot get what you want. 

If you can give us a better idea of what your data looks like and the desired results may be able to help with more specifics. 

[UserInterface · Accepted solution]

That was exactly what i was after. Thanks..

Not sure what happened to formatting on last one. I thought i copied from Notepad++ but maybe i accidently grabbed it from Power BI.. 

Anyway, this was my soultion after using switch like you suggested.

Column = SWITCH(
	'Custom - Requests ++'[Request Status],
	"On Hold","[P]",
	"On Hold - Pending 3rd Party Supplier","[P]3rd Party",
	"On Hold - Pending Known Problem Resolution","[P]Known",
	"On Hold - Pending Site Visit","[P]SSV",
	"On Hold - Pending Customer Feedback","[P]Feedback",
	"None")