DAX formula to extract text before and after special character

[Jenny_Sommet]

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!

[tamerj1 · Accepted solution]

Hi @Jenny_Sommet

Please try

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

[Jenny_Sommet]

@tamerj1  Really appreciate your support!

[Jenny_Sommet]

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)

[tamerj1 · Accepted solution]

@Jenny_Sommet 

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

VAR Length = 

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

[tamerj1 · Accepted solution]

Hi @Jenny_Sommet

Please try

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