dax query to convert first word of rows is capital.

[Shreesefwere]

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

[Uzi2019 · Accepted solution]

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!

[Khushidesai0109 · Accepted solution]

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!!

[Uzi2019]

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 )

I hope I answered your question!

[Khushidesai0109]

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!!

[Shreesefwere]

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.

[RossEdwards]

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?

[Shreesefwere]

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.

[RossEdwards]

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?

[Shreesefwere]

I am trying calculated column

[Khushidesai0109]

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

[Shreesefwere]

 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  " "

[RossEdwards]

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.

[Shreesefwere]

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

[RossEdwards]

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

[Shreesefwere]

can you please read the question

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

[Shreesefwere]

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

[Shreesefwere]

suggestion pls

[Shreesefwere]

suggestion pls

[Uzi2019]

Hi @Shreesefwere 

refer my reply.

[Uzi2019]

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

I hope i answered tour question!

[Shreesefwere]

it is giving invalid token error