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|, ...]

 

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'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.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

  • 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.