Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

SUBSITUTE function does not work correctly in DirectQuery mode

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|, ...]  ...
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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