Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
Solved! Go to 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.
@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?
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:
@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.
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
The errors is:
Function 'SUBSTITUTE' is not allowed as part of calculated column DAX expressions on DirectQuery expressions on DirectQuery models.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
9 | |
7 |