topic Split Text After Second Value with Dax in DAX Commands and Tips

Split Text After Second Value with Dax

hermanos — Tue, 31 Jan 2023 16:42:02 GMT

Hi guys,

I have a sample dataset which contains mail adresses of our users. For example;
car@first@.com

ships@second@.com
car@fa.com
ships@commun.com

If I have "car" in my e-mail address, I want to delete after the first @, if "ships", after the second @.

I tried some functions such as search, mid, len but I couldn't achieve my goal.

I believe there is a lot of wisdom people here. Can you help me about how can I do this with dax?

Thank you!

Re: Split Text After Second Value with Dax

SamInogic — Wed, 01 Feb 2023 06:02:34 GMT

Hi hermanos,

We are bit unclear with exact requirement but as per our understanding, in the Sample data that you have provided there are two "@" in some Emails and you want to remove the 2nd "@" for cars and Ships or else the First from Cars and Second from Ships.

Can you please help us by Elaborating your Requirement in some Sentences so that we can get it and can Provide Result?

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service: http://www.inogic.com/services/

Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/

Re: Split Text After Second Value with Dax

AlB — Wed, 01 Feb 2023 12:12:23 GMT

Hi hermanos

Try this

NewColumn = SWITCH ( TRUE (), SEARCH ( "ships", TableName[Address] ) >= 1, LEFT ( TableName[Address], FIND ( "@", TableName[Address], SEARCH ( "@", TableName[Address] ) + 1 ) - 1 ), SEARCH ( "cars", TableName[Address] ) >= 1, LEFT ( TableName[Address], SEARCH ( "@", TableName[Address] ) - 1 ), TableName[Address] )

Re: Split Text After Second Value with Dax

hermanos — Wed, 01 Feb 2023 13:02:41 GMT

It throws me an error.
"The search Text provided to function 'SEARCH' could not be found in the given text."

Re: Split Text After Second Value with Dax

AlB — Wed, 01 Feb 2023 13:15:45 GMT

Do all your values contain "@"?

Re: Split Text After Second Value with Dax

hermanos — Wed, 01 Feb 2023 13:24:40 GMT

No, some of them has only letters.

Re: Split Text After Second Value with Dax

AlB — Wed, 01 Feb 2023 14:21:50 GMT

hermanos
It would be better to do this in Power Query. Try this other version

NewColumn = SWITCH ( TRUE (), SEARCH ( "ships", TableName[Address],, 0 ) > 0, LEFT ( TableName[Address], VAR aux_ = FIND ( "@", TableName[Address], SEARCH ( "@", TableName[Address],, 0 ) + 1, 0 ) RETURN IF ( aux_ = 0, LEN ( TableName[Address] ), aux_ ) ), SEARCH ( "cars", TableName[Address],, 0 ) > 0, LEFT ( TableName[Address], VAR aux2_ = SEARCH ( "@", TableName[Address],, 0 ) RETURN IF ( aux2_ = 0, LEN ( TableName[Address] ), aux2_ ) ), TableName[Address] )