Solved: Re: dax query to convert first word of rows is ca... - Page 2

Shreesefwere · ‎04-01-2024

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 · ‎04-02-2024

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

View solution in original post

Khushidesai0109 · ‎04-02-2024

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

Proud to be a Super User!!

View solution in original post

Shreesefwere · ‎04-02-2024

same error

Uzi2019 · ‎04-02-2024

hi @Shreesefwere
table name must be in single quotes. you have missed the ' quotes to end table name.

check line by line my code and correct your comma issue.

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

Shreesefwere · ‎04-02-2024

different issue

here is the code

Column3 =

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 )

Uzi2019 · ‎04-02-2024

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