Forum Discussion
DAX - Extract text before second delimeter "-"
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 Try this calculated columnColumn =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
8 Replies
- mh2587
Super User
TextBeforeSecondDelimiter = //Try this VAR FirstDelimiterPosition = FIND("-", [Column], 1, LEN([Column])) VAR SecondDelimiterPosition = FIND("-", [Column], FirstDelimiterPosition + 1, LEN([Column])) RETURN LEFT([Column], SecondDelimiterPosition - 1)mh2587 Thanks for your help, I got the message "An argument of the 'LEFT' function has the wrong data type or an invalid value."
- 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 !!This solution works well except the case CA-OPF_DFEYH
ChiragGarg2512Solution Sage
Anonymous Try this calculated columnColumn =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
