cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rasmusrock Regular Visitor
Regular Visitor

Extract text after/before certain symbol

Hi all,

 

How can i extract the text from each side of a symbol in DAX?

 

I have tried to apply the following formula: =LEFT(B2;(FIND("/";B2;1)-1)) , which works in excel.

 

However, when i use the following formula: =LEFT([GRUPPE];(FIND("/";[GRUPPE];1)-1)) , in powerpivot, referencing the column that i want to look up, it returns the following error:

 

"The search Text provided to function 'FIND' could not be found in the given text"

 

What am i doing wrong?

 

Best regards

 

Rasmus Röck

1 ACCEPTED SOLUTION

Accepted Solutions
PavelR Established Member
Established Member

Re: Extract text after/before certain symbol

@Rasmusrock you have to use the fourth (conditional) parameter of the FIND function - it is used when the text which has to be found is not found - you have data where the "/" is not contained.

 

Then will your formula work.

 

Regards.

Pavel

View solution in original post

4 REPLIES 4
PavelR Established Member
Established Member

Re: Extract text after/before certain symbol

@Rasmusrock you have to use the fourth (conditional) parameter of the FIND function - it is used when the text which has to be found is not found - you have data where the "/" is not contained.

 

Then will your formula work.

 

Regards.

Pavel

View solution in original post

Highlighted
Rasmusrock Regular Visitor
Regular Visitor

Re: Extract text after/before certain symbol

Hi @PavelR,

 

Thank you very much for your reply.

 

I think it solved the initial problem, however, i am stilling getting an error. The error i get now is:

"Calculation error in column 'Varegrupper'[]: Cannot convert value 'Outdoor Living/Flower pots' of type Text to type Integer."

 

For info, 'varegrupper' is the tab i am working in, and 'outdoor living/flower pots' is one of the text strings i am trying to dissolve so i have one column with the 'outdoor living' part and one with the 'flower pots' part.

 

Hope you can help,

 

/Rasmus

 

PavelR Established Member
Established Member

Re: Extract text after/before certain symbol

@Rasmusrock

 

please show me syntax of your whole formula.

 

Pavel

Rasmusrock Regular Visitor
Regular Visitor

Re: Extract text after/before certain symbol

hi @PavelR,

 

I fixed it using your advice, thank you very much 🙂

 

/Rasmus

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 401 members 3,376 guests
Please welcome our newest community members: