cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## dax query to convert  first word of rows is capital.

here is the table

A1

sam disouza

=UPPER(LEFT(A1,SEARCH(" ",A1)-1))&PROPER(MID(A1,FIND(" ",A1),LEN(A1)))

error message unable to find

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

SAME  FOR

Failed to resolve name 'PROPER'. It is not a valid table, variable, or function name.

please note i do not want in transform data option

2 ACCEPTED SOLUTIONS
Super User

Hi @Shreesefwere

just paste below code in calculated column not measure

Column =
VAR _lastName =
MID (
'Person'[# of Person],
SEARCH ( " ", 'Person'[# of Person], 1, 1 ),
LEN ( 'Person'[# of Person] ) - 1
)
VAR _firstName =
MID ( 'Person'[# of Person], 1, SEARCH ( " ", 'Person'[# of Person], 1, 1 ) - 1 )
RETURN
UPPER ( LEFT ( _firstName, 1 ) )
& RIGHT ( _firstName, LEN ( _firstName ) - 1 ) & " "
& UPPER ( LEFT ( TRIM ( _lastName ), 1 ) )
& RIGHT ( TRIM ( _lastName ), LEN ( TRIM ( _lastName ) ) - 1 )

I hope this time you may get correct answer!

Don't forget to give thumbs up and accept this as a solution if it helped you!!!
Resolver I
Spoiler
Spoiler

`Hiii for new column just add the following Dax `
`CapitalizedFullNameff = VAR SplitName =    TRIM ( NAmer[Column1] )VAR FirstSpace =    SEARCH ( " ", SplitName, 1, LEN ( SplitName ) )VAR FirstWord =    LEFT ( SplitName, FirstSpace - 1 )VAR RestOfName =    MID ( SplitName, FirstSpace + 1, LEN ( SplitName ) - FirstSpace )RETURN    UPPER ( LEFT ( FirstWord, 1 ) )        & LOWER ( RIGHT ( FirstWord, LEN ( FirstWord ) - 1 ) ) & " "        & UPPER ( LEFT ( RestOfName, 1 ) )        & LOWER ( RIGHT ( RestOfName, LEN ( RestOfName ) - 1 ) )`
Don't forget to give thumbs up and accept this as a solution if it helped you!!

24 REPLIES 24
Resolver I
Spoiler
Spoiler

`Hiii for new column just add the following Dax `
`CapitalizedFullNameff = VAR SplitName =    TRIM ( NAmer[Column1] )VAR FirstSpace =    SEARCH ( " ", SplitName, 1, LEN ( SplitName ) )VAR FirstWord =    LEFT ( SplitName, FirstSpace - 1 )VAR RestOfName =    MID ( SplitName, FirstSpace + 1, LEN ( SplitName ) - FirstSpace )RETURN    UPPER ( LEFT ( FirstWord, 1 ) )        & LOWER ( RIGHT ( FirstWord, LEN ( FirstWord ) - 1 ) ) & " "        & UPPER ( LEFT ( RestOfName, 1 ) )        & LOWER ( RIGHT ( RestOfName, LEN ( RestOfName ) - 1 ) )`
Don't forget to give thumbs up and accept this as a solution if it helped you!!

Super User

Hi @Shreesefwere
Try below dax calculated column

Column =
VAR _lastName =
MID (
'Table upper'[Column1],
SEARCH ( " ", 'Table upper'[Column1], 1, 1 ),
LEN ( 'Table upper'[Column1] ) - 1
)
VAR _firstName =
MID ( 'Table upper'[Column1], 1, SEARCH ( " ", 'Table upper'[Column1], 1, 1 ) - 1 )
RETURN
UPPER ( LEFT ( _firstName, 1 ) )
& RIGHT ( _firstName, LEN ( _firstName ) - 1 ) & " "
& UPPER ( LEFT ( TRIM ( _lastName ), 1 ) )
& RIGHT ( TRIM ( _lastName ), LEN ( TRIM ( _lastName ) ) - 1 )

Don't forget to give thumbs up and accept this as a solution if it helped you!!!
Resolver I

This dax for calculated column/ new column

CapitalizedFullNameff =
VAR SplitName =
TRIM ( NAmer[Column1] )
VAR FirstSpace =
SEARCH ( " ", SplitName, 1, LEN ( SplitName ) )
VAR FirstWord =
LEFT ( SplitName, FirstSpace - 1 )
VAR RestOfName =
MID ( SplitName, FirstSpace + 1, LEN ( SplitName ) - FirstSpace )
RETURN
UPPER ( LEFT ( FirstWord, 1 ) )
& LOWER ( RIGHT ( FirstWord, LEN ( FirstWord ) - 1 ) ) & " "
& UPPER ( LEFT ( RestOfName, 1 ) )
& LOWER ( RIGHT ( RestOfName, LEN ( RestOfName ) - 1 ) )

If this help Please give thumbs up and accept it as solution!!
Helper IV

yes also tried below query but not working

FullName = UPPER ( LEFT ( A1, 1 ) )
& LOWER ( RIGHT ( A1, LEN ( A1) - 1 ) )
error message An argument of function 'RIGHT' has the wrong data type or has an invalid value.
Solution Sage

The formula in your post looks very Excel like and not something you'd find using DAX.  I'm a little unsure what you are trying to achieve.  Are you trying to make a DAX calculated column or are you trying to write a measure to come up with an answer for a report?

Helper IV

yes also tried below query but not working

FullName = UPPER ( LEFT ( A11 ) )
LOWER ( RIGHT ( A1LEN ( A1) - 1 ) )
error message An argument of function 'RIGHT' has the wrong data type or has an invalid value.
Solution Sage

Of course that won't work in DAX. DAX doesn't work like that.  The way the formula is written, it looks like its trying to reference an Excel Cell, A1 in this case.

If you are trying to write a calculated column or a measure, you'll use DAX differently.  What you are trying to do matters here, so you need to describe what you are trying to achieve?

Do you have data in your Power BI model that you want to clean up in a new column?  Are you writing a measure to change the formatting of the TEXT at run time?

Helper IV

I am trying calculated column

Resolver I

You can do that transformation in power query as well that would be lot easy else you have other option that is to create a measure

Helper IV

getting error message ... REPLACE A1 with real table name

FullName = FIND(" ", SELECTEDVALUE(Person[# of Person]), 1, 1)
RETURN UPPER(LEFT(SELECTEDVALUE(Person[# of Person]), 1)) & LOWER(MID(SELECTEDVALUE(Person[# of Person]), 2, FirstSpace - 1)) & MID(SELECTEDVALUE(Person[# of Person], " " + 1, LEN(SELECTEDVALUE('Person[# of Person])) - " ")

The syntax for 'RETURN' is incorrect. the firstspace is repalaced with  " "

Solution Sage

You still haven't said what you are trying to actually do.  I realise someone else has given you some code that you can't get to work but its hard to give you useful advice if you ignore the basic question.

Helper IV

Hi,

want to get first letter of word in capital like  i have column fullname ' sam disilva'

so expected data is  Sam Disilva  in DAX Query only

Solution Sage

Are you looking to do this as the report loads or are you trying to do this when the data refreshes and store this inside your table?

If you are trying to store this as a table, i'd very much recommend using Transforma Data and the "Text.Proper" function.  That is the easiest way to just convert the data and be done with it.

If you are trying to right a measure to do it at runtime, thats going to be harder since DAX lacks the PROPER function.

Here is another thread with some code that might assist:

Solved: DAX equivalent to the Excel Proper function - Microsoft Fabric Community

Helper IV

Hi,

want to get first letter of word in capital like  i have column fullname ' sam disilva'

so expected data is  Sam Disilva  in DAX Query only

Helper IV

Hi,

want to get first letter of word in capital like  i have column fullname ' sam disilva'

so expected data is  Sam Disilva  in DAX Query only

Helper IV

suggestion pls

Helper IV

suggestion pls

Super User

Hi @Shreesefwere

Don't forget to give thumbs up and accept this as a solution if it helped you!!!
Super User

Try below dax calculated column

Column =
VAR _lastName =
MID (
'Table upper'[Column1],
SEARCH ( " ", 'Table upper'[Column1], 11 ),
LEN ( 'Table upper'[Column1] ) - 1
)
VAR _firstName =
MID ( 'Table upper'[Column1], 1SEARCH ( " ", 'Table upper'[Column1], 11 ) - 1 )
RETURN
UPPER ( LEFT ( _firstName, 1 ) )
& RIGHT ( _firstName, LEN ( _firstName ) - 1 ) & " "
& UPPER ( LEFT ( TRIM ( _lastName ), 1 ) )
& RIGHT ( TRIM ( _lastName ), LEN ( TRIM ( _lastName ) ) - 1 )

I hope i answered tour question!

Don't forget to give thumbs up and accept this as a solution if it helped you!!!
Helper IV

it is giving invalid token error

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors