DAX extract text between delimiter

[Anonymous]

Hi, 

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

AAA=BBB,CCC=DDD;EEE=FFF

[AntrikshSharma]

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

[harshnathani]

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!

[Anonymous]

 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

[harshnathani · Accepted solution]

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!

[nomi]

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.