Forum Discussion
Splitting Text on multiple delimiters with DAX
I have a table with 10 million+ records. One table contains a text field with multiple sentences in each cell.
My goal is to let the user select one record, and then split its sentences by a specific delimiter with DAX.
I managed to do this in Power Query (Text.Split) when testing with a smaller dataset, however with this method I have to split the texts of every single record. Given the large size of the data this freezes Power BI Desktop. This I why I would like to to this on the fly, with DAX.
The following picture explains my thought process:
By drilling through, I can select only one of the records. Then, on Page 2, a DAX calculated collumn/measure would run only on this selected text. So far, I have managed to drill through to a single record, however I do not know how to create an on the fly running, text splitting DAX function.
Does anyone know how to to this with DAX? I am open to other solutions as well. The splitting needs to happen on the fly, on a user selected record.
Hi Anonymous ,
have a look at the solution in the attached file.
4 Replies
- mwegener
Most Valuable Professional
Hi Anonymous ,
try this
Thank you mwegener! This method almost solved my problem. I need to do one additional thing.
Now the sentences are all on new lines, however I also need them in different cells. Something like this:
I have to put the sentences in different records. Is there an on the fly function for this?
- mwegener
Hi Anonymous ,
have a look at the solution in the attached file.