Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

 

 

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Community Champion
Community Champion

@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?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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 🙂 

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.