topic Re: How to split First Name and Last Name fromFull Name with" ," as delimiter using DAX in Direct Query in DAX Commands and Tips

How to split First Name and Last Name fromFull Name with" ," as delimiter using DAX in Direct Query

Mahipal — Fri, 22 Mar 2024 10:32:58 GMT

We have a requirement to split First and Last name from Fullname in direct query mode using DAX.

Re: How to split First Name and Last Name fromFull Name with" ," as delimiter using DAX in Direct Query

Dangar332 — Fri, 22 Mar 2024 11:36:37 GMT

hi, Mahipal

try below code for measure

Firstname = LEFT( MIN('Table (2)'[Name]), SEARCH(",",MIN('Table (2)'[Name]))-1 )

Last name = RIGHT( MIN('Table (2)'[Name]), LEN(MIN('Table (2)'[Name]))-SEARCH(",",MIN('Table (2)'[Name])) )

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to split First Name and Last Name fromFull Name with" ," as delimiter using DAX in Direct Query

Mahipal — Fri, 22 Mar 2024 12:40:21 GMT

Hello Dangar332 your Query is working but results are not perfect. I am seeing same Name for all records.

Re: How to split First Name and Last Name fromFull Name with" ," as delimiter using DAX in Direct Query

Dangar332 — Fri, 22 Mar 2024 12:53:20 GMT

hi, Mahipal

you have any Unique identity on which we can add first name and last name in all rows.
and why cash,johnny only is there any specific condition?

Re: How to split First Name and Last Name fromFull Name with" ," as delimiter using DAX in Direct Query

Mahipal — Fri, 22 Mar 2024 13:04:11 GMT

I am using same query which you sent here. But the result is only one name. Here is query

Firstname =

LEFT(

MIN('Users'[User Name]),

SEARCH(",",MIN('Users'[User Name]))-1

)

Last name =

RIGHT(

MIN('Users'[User Name]),

LEN(MIN('Users'[User Name]))-SEARCH(",",MIN('Users'[User Name]))

)

I need all names in results properly.

Re: How to split First Name and Last Name fromFull Name with" ," as delimiter using DAX in Direct Query

Mahipal — Fri, 22 Mar 2024 13:45:32 GMT

If i modified that query from

Firstname =

LEFT(

MIN('Users'[User Name]),

SEARCH(",",MIN('Users'[User Name]))-1

)

Firstname =

LEFT(

('Users'[User Name]),

SEARCH(",",MIN('Users'[User Name]))-1

)

I am still not getting proper results.

Re: How to split First Name and Last Name fromFull Name with" ," as delimiter using DAX in Direct Query

Anonymous — Mon, 25 Mar 2024 06:12:35 GMT

Hi Mahipal ,

I made simple samples and you can check the results below:

First Name = PATHITEM(SUBSTITUTE(MAX('Table'[User Name]),",","|"),1) Last Name = PATHITEM(SUBSTITUTE(MAX('Table'[User Name]),",","|"),2)

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to split First Name and Last Name fromFull Name with" ," as delimiter using DAX in Direct Query

Mahipal — Wed, 27 Mar 2024 09:05:49 GMT

Hello,

Its working fine for import mode. Here we are using direct mode so the output is different as in the below image in direct mode

Can you find the query for direct mode.

Re: How to split First Name and Last Name fromFull Name with" ," as delimiter using DAX in Direct Query

Anonymous — Wed, 27 Mar 2024 09:29:25 GMT

Hi Mahipal ,

It's strange, I used the solution Dangar332 provided and I was able to get the right data.Did you create a calculated column instead of a measure?

Best regards,
Community Support Team_ Scott Chang

Re: How to split First Name and Last Name fromFull Name with" ," as delimiter using DAX in Direct Query

Mahipal — Wed, 27 Mar 2024 11:15:59 GMT

Thank you so much. Yes as measure this query is working. But I would like to have a query for Calculated Calumn as per our requirement.

Re: How to split First Name and Last Name fromFull Name with" ," as delimiter using DAX in Direct Query

Anonymous — Thu, 28 Mar 2024 01:10:19 GMT

Hi Mahipal ,

As far as I know, there doesn't seem to be a way for this to be realized by calculating columns.

Best regards,
Community Support Team_ Scott Chang