topic Re: DAX formula to extract text before and after special character in DAX Commands and Tips

DAX formula to extract text before and after special character

Jenny_Sommet — Mon, 13 Mar 2023 15:39:00 GMT

Hello!

I am new to the community and hoping for some help! In the column below, I only want to extract the text that comes after '- ' and before ' -' for the entire column of values. Please can you help with DAX calculation? I am unable to use PowerQuery for various reasons.

EDPC - 2023.Q1 - DP (want to extract 2023.Q1)

LRM - 2023.1 - UG - DP (want to extract 2023.1)

LRCM - 2022.2 - PG - DP (want to extract 2022.2)

Thank you for your help!

Re: DAX formula to extract text before and after special character

tamerj1 — Mon, 13 Mar 2023 15:52:57 GMT

Hi Jenny_Sommet

Please try

Column2 =
VAR String = 'Table'[Column1]
VAR Items =
SUBSTITUTE ( String, " - ", "|" )
RETURN
PATHITEM ( Items, 2 )

Re: DAX formula to extract text before and after special character

Jenny_Sommet — Mon, 13 Mar 2023 15:58:26 GMT

Thank you so much for the fast response! tamerj1

Can you confirm how I can modify the DAX to only extract the characters after '- ' also?

EDPC - 2023.Q1 - DP (want to extract DP)

LRM - 2023.1 - UG - DP (want to extract DP)

LRCM - 2022.2 - PG - DP (want to extract DP)

Re: DAX formula to extract text before and after special character

tamerj1 — Mon, 13 Mar 2023 16:03:17 GMT

Jenny_Sommet

Column2 =
VAR String = 'Table'[Column1]
VAR Items =
SUBSTITUTE ( String, " - ", "|" )

VAR Length =

COALESCE ( PATHLENGTH ( Items ), 1 )
RETURN
PATHITEM ( Items, Length )

Re: DAX formula to extract text before and after special character

Jenny_Sommet — Mon, 13 Mar 2023 16:20:33 GMT

tamerj1 Really appreciate your support!