Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vnqt
Helper V
Helper V

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 

1 ACCEPTED SOLUTION
ChiragGarg2512
Super User
Super User

@vnqt 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

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

Screenshot (34).png

ChiragGarg2512
Super User
Super User

@vnqt 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

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
Not applicable

here is the dax query for Screenshot (34).png

johnbasha33
Solution Sage
Solution Sage

@vnqt  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 !!

@johnbasha33 

This solution works well except the case CA-OPF_DFEYH

mh2587
Super User
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)

 


Did I answer your question? If so, please mark my post as a solution!


Proud to be a Super User!




LinkedIn Icon
Muhammad Hasnain



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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.