topic Re: DAX extract text between delimiter in DAX Commands and Tips

DAX extract text between delimiter

Anonymous — Tue, 30 Jun 2020 08:02:49 GMT

Hi,

How can I get the red text below in DAX? A text after equal and before semicolon

AAA=BBB,CCC=DDD;EEE=FFF

Re: DAX extract text between delimiter

Anonymous — Tue, 30 Jun 2020 08:16:35 GMT

HI Anonymous ,

Does all your column have the same pattern? i.e an = and ;

You can also have a look at these

https://community.powerbi.com/t5/Desktop/Extract-text-Between-Delimiters-with-varying-text/td-p/670340

https://www.wiseowl.co.uk/blog/s2535/text-between-delimiters.htm

https://community.powerbi.com/t5/Desktop/DAX-extracting-string-using-delimiter/td-p/287840

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

Re: DAX extract text between delimiter

Anonymous — Tue, 30 Jun 2020 17:31:22 GMT

yes same pattern

the first and second post are power query which is not what i need

the third post, I tried to replicate but failed

Re: DAX extract text between delimiter

Anonymous — Tue, 30 Jun 2020 17:50:49 GMT

Hi Anonymous ,

Create a Calculated Column

First Derived Column = VAR FirstEqual = FIND ( "=", 'Table'[Sorting Text], 1 ) VAR SecondEqual = FIND ( "=", 'Table'[Sorting Text], FirstEqual + 1 ) VAR Thirdampersand = FIND ( ";", 'Table'[Sorting Text], SecondEqual + 1 ) RETURN MID ( 'Table'[Sorting Text], SecondEqual + 1, Thirdampersand - SecondEqual - 1 )

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

Re: DAX extract text between delimiter

AntrikshSharma — Tue, 30 Jun 2020 19:10:56 GMT

You can try this too for learning new trick:

GetText = VAR OriginalText = 'Table'[Column1] VAR CountOfEqualSign = LEN ( 'Table'[Column1] ) - LEN ( SUBSTITUTE ( 'Table'[Column1], "=", "" ) ) VAR AddCaret = SUBSTITUTE ( OriginalText, "=", "^", CountOfEqualSign - 1 ) VAR FirstCharAfterCaret = SEARCH ( "^", AddCaret ) + 1 VAR SemiColonPostion = SEARCH ( ";", OriginalText ) VAR Result = MID ( AddCaret, FirstCharAfterCaret, SemiColonPostion - FirstCharAfterCaret ) RETURN Result

Re: DAX extract text between delimiter

Anonymous — Wed, 16 Nov 2022 07:19:18 GMT

Hi Harsh, I am having a similar situation. I want to extract the string between "/" in the longer string, such as: "abc/def/ghijk". I used the same formula you gave and substituted with my own variables:

Division =

VAR FirstLevel =

FIND ("/", GetFolders[FullyQualifiedName], 1)

VAR SecondLevel =

FIND ("/", GetFolders[FullyQualifiedName], FirstLevel + 1)

RETURN

MID (GetFolders[FullyQualifiedName], FirstLevel + 1, SecondLevel - 1)

The error I am getting is: "The search Text provided to function 'FIND' could not be found in the given text." I am suspecting that this is because not all strings have 2 "/". Some have 1 and some don't have any at all. I am only interested in the ones with 2 "/" and the rest can be just blank or empty.