DAX - Extract text before second delimeter "-"

[Anonymous]

Hi,

I have a calculated column as below

CA-ORG-SDV-FRESZ

CA-OPN-SDR

CA-SRT-RZS-gSRE

OFFICE

OTHER

I would like to export the text before the second delimeter with DAX , the result would be like this :

CA-ORG

CA-OPN

CA-SRT

OFFICE

OTHER

Many thanks in advance.

Tg 

[Anonymous]

[ChiragGarg2512 · Accepted solution]

@Anonymous Try this calculated column

Column =

var _path = SUBSTITUTE('Table'[Column1], "-", "|")

var _litems = PATHITEM(_path, 1, TEXT)

var _litems1 = PATHITEM(_path, 2, TEXT)

var _value = if(_litems1 = "", _litems, _litems & "-" & _litems1)

return

_value

[Anonymous]

Hi, this works but I have 1 case that didn't work 

CA-OPF_DFEYH

Result is always the same . 

Could you please advise ? 

Tg 

[Anonymous]

here is the dax query for 

[johnbasha33]

@Anonymous  use this measure and replace with your actual table name and column name
ExtractedText =
VAR FirstDelimiterPos = FIND("-", 'YourTable'[YourColumn], 1, LEN('YourTable'[YourColumn]))
VAR SecondDelimiterPos = FIND("-", 'YourTable'[YourColumn], FirstDelimiterPos + 1, LEN('YourTable'[YourColumn]))
VAR Length = SecondDelimiterPos - FirstDelimiterPos - 1
RETURN
IF(
SecondDelimiterPos > 0,
LEFT('YourTable'[YourColumn], SecondDelimiterPos - 1),
'YourTable'[YourColumn]
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[Anonymous]

@johnbasha33 

This solution works well except the case CA-OPF_DFEYH

[mh2587]

TextBeforeSecondDelimiter = //Try this
VAR FirstDelimiterPosition = FIND("-", [Column], 1, LEN([Column]))
VAR SecondDelimiterPosition = FIND("-", [Column], FirstDelimiterPosition + 1, LEN([Column]))
RETURN LEFT([Column], SecondDelimiterPosition - 1)

[Anonymous]

@mh2587 Thanks for your help, I got the message "An argument of the 'LEFT' function has the wrong data type or an invalid value."