Forum Discussion
RIGHT, SEARCH, LEN only providing partial string outputs
Good day,
I've searched the forums and the web and I'm hitting a wall with something which seems so straight forward, or I'm missing something.
The image below contains the DAX formula, the original "Text" and current "Output":
Output = RIGHT('Table'[Text], SEARCH(": ",'Table'[Text],1,LEN('Table'[Text])))
Desired outcome: I have a large data set with variable string lengths to the left of the "Text" column, which is luckily always separated by a ":" and in some of the column rows, there might be text with no pre-text separated by a delimiter, so then it should just output the entire string (as per the "correct" outputs above).
Any help or suggestions to achieve my desired outcomes would be greatly appreciated.
Thanks!
Anonymous
please try this
Column = if(SEARCH(":",'Table'[TEXT],1,0)=0,'Table'[TEXT],right('Table'[TEXT],len('Table'[TEXT])-SEARCH(":",'Table'[TEXT],1)))
6 Replies
ryan_mayuSuper User
Anonymous
please try this
Column = right('Table'[TEXT],len('Table'[TEXT])-SEARCH(":",'Table'[TEXT],1))Hi ryan_mayu;
Thanks for your prompt response, your proposal works, BUT only if a delimiter is present in the column. As soon as there is no delimiter present, I receive the following error message:
The search Text provided to function 'SEARCH' could not be found in the given text.As part of my OP, there will be rows without text being separated by a delimiter, but I would then just want it to display in the column, as per the "Correct" elements in the image of my OP. A combination of both what is needed for a solution.
amitchandak, as mentioned above, simply separating by delimiter does not solve the issue as it struggles to handle the aforementioned.
Any suggestions?
- amitchandak
Anonymous , In case you are trying to split by delimiter, better do it in Power Query, refer
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
