topic Re: SUBSITUTE function does not work correctly in DirectQuery mode in DAX Commands and Tips

SUBSITUTE function does not work correctly in DirectQuery mode

Anonymous — Tue, 10 Jan 2023 14:34:38 GMT

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! 🙂

Re: SUBSITUTE function does not work correctly in DirectQuery mode

Anonymous — Tue, 10 Jan 2023 14:45:01 GMT

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

Re: SUBSITUTE function does not work correctly in DirectQuery mode

Greg_Deckler — Tue, 10 Jan 2023 14:45:38 GMT

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?

Re: SUBSITUTE function does not work correctly in DirectQuery mode

Anonymous — Tue, 10 Jan 2023 14:55:50 GMT

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 🙂

Re: SUBSITUTE function does not work correctly in DirectQuery mode

Greg_Deckler — Tue, 10 Jan 2023 15:16:50 GMT

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.

Re: SUBSITUTE function does not work correctly in DirectQuery mode

Anonymous — Thu, 12 Jan 2023 08:27:40 GMT

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