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!

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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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.