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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Mahipal
Frequent Visitor

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

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

 

10 REPLIES 10
v-tianyich-msft
Community Support
Community Support

Hi @Mahipal ,

 

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

vtianyichmsft_0-1711347026263.png

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.

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

Mahipal_0-1711528790445.png

Can you find the query for direct mode.

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

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. 

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

Mahipal
Frequent Visitor

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

Mahipal_1-1711111193462.png

 

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?

 

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. 
Dangar332
Super User
Super User

hi, @Mahipal 

try below code for measure 

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

 

Dangar332_0-1711107295537.png

 

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

 

Dangar332_1-1711107330257.png

 

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

 

If i modified that query from 

Firstname = 
 LEFT(
    MIN('Users'[User Name]),
    SEARCH(",",MIN('Users'[User Name]))-1
)
to 
Firstname = 
 LEFT(
    ('Users'[User Name]),
    SEARCH(",",MIN('Users'[User Name]))-1

 

Mahipal_0-1711115020791.png

I am still not getting proper results.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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