Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Concatenate with Order

Hi everyone, i have a problem with DAX syntaxt.

I would like to concatenate 2 columns into a new column but with a specific order. Let's make an example.
Suppose that we have into table A the columns: Names, Ages

NamesAges
Lucas, Walter, Marius Uan, Alex23,25,45,44
Debora21

 

I would like to calculate a new column called "Names and Ages" like that:
Lucas(23), Walter(25), Marius Uan(45), Alex(44)
Debora(21)



Could someone help me?
Thanks a lot in advice ❤️

  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    Anonymous Try this:

    Column = 
    VAR __NamesTable = SUBSTITUTE(SUBSTITUTE([Names], " ", ""),",", "|")
    VAR __AgesTable = SUBSTITUTE(SUBSTITUTE([Ages], " ", ""),",", "|")
    VAR __Length = PATHLENGTH(__NamesTable)
    VAR __Table = 
        ADDCOLUMNS(
            GENERATESERIES(1, __Length, 1),
            "__Text", PATHITEM( __NamesTable, [Value]) & "(" & PATHITEM( __AgesTable, 1) & ")"
        )
    VAR __Result = CONCATENATEX( __Table, [__Text], ", " )
RETURN
    __Result

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Anonymous Try this:

    Column = 
    VAR __NamesTable = SUBSTITUTE(SUBSTITUTE([Names], " ", ""),",", "|")
    VAR __AgesTable = SUBSTITUTE(SUBSTITUTE([Ages], " ", ""),",", "|")
    VAR __Length = PATHLENGTH(__NamesTable)
    VAR __Table = 
        ADDCOLUMNS(
            GENERATESERIES(1, __Length, 1),
            "__Text", PATHITEM( __NamesTable, [Value]) & "(" & PATHITEM( __AgesTable, 1) & ")"
        )
    VAR __Result = CONCATENATEX( __Table, [__Text], ", " )
RETURN
    __Result