Forum Discussion

jcastr02's avatar
jcastr02
Post Prodigy
6 years ago
Solved

joining columns together (name)

I had a first name and last name column which I joined to "Name of team member", and then did a split column since the team members middle initial   was at beginning of last name ex.   Amy west...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi jcastr02 ,

     

    You need 2 measures as below:

     

    Name of team member 1 =
VAR a =
    ISERROR ( SEARCH ( ".", SELECTEDVALUE ( 'Table'[Last Name] ) ) )
RETURN
    IF (
        a = TRUE (),
        CONCATENATE (
            SELECTEDVALUE ( 'Table'[Last Name] ) & ",",
            SELECTEDVALUE ( 'Table'[First Name] )
        ),
        LEFT ( SELECTEDVALUE ( 'Table'[Last Name] ), 1 )
    )
    Name of team member 2 =
VAR a =
    ISERROR ( SEARCH ( ".", SELECTEDVALUE ( 'Table'[Last Name] ) ) )
VAR b =
    LEN ( SELECTEDVALUE ( 'Table'[Last Name] ) )
        - SEARCH ( ".", SELECTEDVALUE ( 'Table'[Last Name] ),, 0 )
VAR c =
    RIGHT ( SELECTEDVALUE ( 'Table'[Last Name] ), b )
RETURN
    IF (
        a = FALSE (),
        CONCATENATE ( c & ",", SELECTEDVALUE ( 'Table'[First Name] ) ),
        BLANK ()
    )

     

     

    Finally you will see:

     

     

    For the related .pbix file,pls click here.

     

    Best Regards,
    Kelly
     
    Did I answer your question? Mark my post as a solution!

     

     

  • jcastr02's avatar
    jcastr02
    Post Prodigy
    6 years ago

    amitchandak  How can I just get value that has the name into one column?  If I concatenate, it will bring the values together, example west, amy should just show  in new column as West, Amy     (want to it disregard the "m")