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
Icon for Community Champion rankCommunity 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
      Icon for Community Champion rankCommunity 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