Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX extract text between delimiter

Hi, 

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

AAA=BBB,CCC=DDD;EEE=FFF

  • Anonymous's avatar
    Anonymous
    6 years ago

    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!

5 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    6 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

       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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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!