Split Text After Second Value with Dax

[hermanos]

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!

[AlB]

@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]
)

[AlB]

Do all your values contain "@"?

[hermanos]

No, some of them has only letters.

[AlB]

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]
)

[hermanos]

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

[SamInogic]

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/