SUBSITUTE function does not work correctly in DirectQuery mode

[Anonymous]

Hello everyone,

I would like to find a solution for a problem regarding the SUBSITUTE function.

I have a string of procceses in the following format:

[|Process1|,|Process2|,|Proces3|, ...]

I would like to split this string into multiple columns in order to have each process in a separat column. I find the solution, but when I tried to integrate it on one project that uses DirectQuery as Storage Mode I realize that my logic does not work because the SUBSTITUTE function accept only 3 parameters, not 4 as it works in the Import Mode.

The syntax I used is the following:

SUBSTITUTE(Table1[Processes_0],"|,|","*",1)

It does not work on DirectQuery due to the 4th parameter. I need to replace that grouping of symbols "|,|" based on the number of the instance. 

Could you help me please with a solution to the problem presented above?

Thank you! 🙂

[Greg_Deckler]

@Anonymous Why can't you just turn it into a PATH by replacing each instance with "|". Why do you need to replace each one individually?

[Anonymous]

Hi @Greg_Deckler ,

Thank you for your message!
For example, in order to extract the second process from a string that contains 6 processes the code is the following:

PR2 = IF(Table1[NumberOfProcesses]<=1,

"",

    var aux1 = SUBSTITUTE(Table1[Processes_0],"|,|","*",1)

    var aux2 = SUBSTITUTE(aux1,"|","*",2)

    var First = FIND("*",aux2,1,0)

    var Last = FIND("*",aux2,First+1,500)

    var Diff = Last-First-1

    var Final = MID(aux2,First+1,Diff)

return Final)

Is the case more clear now?

Thank you,

Diana 🙂 

[Greg_Deckler · Accepted solution]

@Anonymous Any chance you can use a measure? More DAX functions are supported in measures rather than calculated columns. Also, if you can use SUBSTITUTE in a measure and replace all occurences with "|" (pipe character) then you can use PATHITEM to retrieve individual parts instead of using FIND, MID, etc.

[Anonymous]

Hi @Greg_Deckler ,

Thank you a lot for the solution.

I used the PATHITEM function to split my string in multiple columns.

Have a wonderful day!
Dia

[Anonymous]

The errors is: 
Function 'SUBSTITUTE' is not allowed as part of calculated column DAX expressions on DirectQuery expressions on DirectQuery models.