how to remove repetitive last name from full name using DAX

[Anonymous]

Hello!

I have a field with users' fullname, which some of them have repetitive last name, pls see some examples below. I need to clean this data using DAX. Please help me how to make them.

full name              expected full name

John Linx               John Linx

Marie Stb Stb        Marie Stb 

Louis T NX NX       Louis T NX

Jan S Med             Jan S Med

[jaweher899]

You can use the DAX function SUBSTITUTE() to remove repetitive last names from full names in a column. Here is an example of how you can use this function to remove repetitive last names in a column called "Full Name":

1. Create a new column called "Clean Full Name" in your data table.

2. In the "Clean Full Name" column, use the DAX formula:

   =SUBSTITUTE(Full Name,[Full Name],RIGHT([Full Name],LEN([Full Name])-FIND(" ",Full Name,FIND(" ",Full Name)+1))," ")

3. This formula uses the SUBSTITUTE function to replace the repetitive last name with a space character. The RIGHT function is used to extract the last name from the "Full Name" column, and the FIND function is used to locate the space character between the first and last name.

4. Once the formula is in place, you can use the new "Clean Full Name" column in your dashboard or report instead of the original "Full Name" column.

Note: The above formula would work for the examples you've provided, but in case you have a different format of the full name, you might have to tweak the formula a bit.

[Jihwan_Kim · Accepted solution]

Hi,

One of ways to achieve this is to use PATHITEM DAX function with "|".

Please check the below picture and the attached pbix file.

Expected full name CC = 
VAR _separate =
    SUBSTITUTE ( Data[Full Name], " ", "|" )
VAR _nameslist =
    UNION (
        { PATHITEM ( _separate, 1 ) },
        { PATHITEM ( _separate, 2 ) },
        { PATHITEM ( _separate, 3 ) },
        { PATHITEM ( _separate, 4 ) }
    )
VAR _summarizelist =
    SUMMARIZE ( _nameslist, [Value] )
RETURN
    CONCATENATEX ( _summarizelist, [Value], " " )

[Anonymous]

Thank you! It is a very brilliant idea of using pathitem!