DAX - CONCATENATEX - use a different delimiter at the end

[clk2023]

Hi all, 

hope some one can help.

currently I'm getting the following result when I use CONCATENATEX function.

CONCATENATEX (Employee, [Employee Name],", ", [Employee Key], ASC)
returns:

Rob, Rose, Mike, Lisa

I would like it to return : 

Rob, Rose, Mike and Lisa

The second option looks better grammatically on a card.. Can anyone show me how to achieve this?

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

CONCATENATEX.pbix

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

[OwenAuger]

Is Employee a physical table (rather than a variable)?

One option in addition to those presented is:

Concat Measure =
VAR MinKey =
    MIN ( Employee[Employee Key] )
VAR MaxKey =
    MAX ( Employee[Employee Key] )
RETURN
    CONCATENATEX (
        Employee,
        VAR Prefix =
            SWITCH ( Employee[Employee Key], MinKey, "", MaxKey, " and ", ", " )
        RETURN
            Prefix & Employee[Employee Name],
        ,
        Employee[Employee Key]
    )

[Ahmedx]

pls try this

Measure = 
VAR _t1= CONCATENATEX (Employee, [Employee Name],", ", [Employee Key], ASC)
VAR _t2 =  PATHLENGTH(SUBSTITUTE(_t1,", ","|"))
VAR _t3 = SUBSTITUTE(_t1,", ","|",_t2-1)
VAR _t4 = SUBSTITUTE(_t3,"|"," And ")
RETURN
_t4
----------------or-----------------
Measure = 
VAR _t1= CONCATENATEX (Employee, [Employee Name],", ", [Employee Key], ASC)
VAR _t2 =  PATHLENGTH(SUBSTITUTE(_t1,",","|"))
VAR _t3 = SUBSTITUTE(_t1,", "," And ",_t2-1)
RETURN
_t3

[Daniel_PowerBI]

Thank you for the measure, works great but you need to add this to t3 and t4 for it to also work when only 1 choice is selected (ie: no "," for the substitute to find so it throws an error)

Measure = 
VAR _t1= CONCATENATEX (Employee, [Employee Name],", ", [Employee Key], ASC)
VAR _t2 =  PATHLENGTH(SUBSTITUTE(_t1,", ","|"))
VAR _t3 = IF(_t2 = 1, _t1 ,SUBSTITUTE(_t1,", ","|",_t2-1))
VAR _t4 = IF(_t2 = 1, _t1 ,SUBSTITUTE(_t3,"|"," And "))
RETURN
_t4

[DOLEARY85]

Hi,

try this in a calculated column:

Column = var concat = CONCATENATEX ('Table', [Column1],", ", 'Table'[Column1], ASC)

var length = LEN(concat)-LEN(SUBSTITUTE(concat,",",""))

return

SUBSTITUTE(concat,",", " and",length)

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[TomMartens · Accepted solution]

Hey @clk2023 ,

I use this measure to create the string:

List of Names = 
var InitialList_PATH= 
    CONCATENATEX(
        VALUES( Somenames[Name] )
        , 'Somenames'[Name]
        , "|"
        , 'Somenames'[Name]
        , ASC
    )
var lengthOfPath = PATHLENGTH( InitialList_PATH )    
var theString = 
    CONCATENATEX(
        ADDCOLUMNS(
            GENERATESERIES(
                1 , lengthOfPath, 1
            )
            , "NamesWithSeparators"
                , IF( [Value] < lengthOfPath - 1
                    , CONCATENATE( PATHITEM( InitialList_PATH , [Value] , TEXT) , ", ")
                    , IF( [Value] = lengthOfPath -1 
                        , CONCATENATE( PATHITEM( InitialList_PATH , [Value] , TEXT) , " and ")
                        , PATHITEM( InitialList_PATH , [Value] , TEXT)
                    )
                )
        )
        , [NamesWithSeparators]
    )
return theString

It starts with concatenation using concatenatex, but with the special character |, this character turns a string into a path, meaning it's possible to some of the PATH... functions.
I use GENERATEX to create a virtual table with as many rows as there are items in the path. I add column where I apply a separator to each item (the name) based on the position [value] in the tabel. Then I use CONCATENATEX around the virtual table but this time without specifying a separator.
A sample picture:

Hopefully, this provides what you are looking for.

Regards,
Tom

[clk2023]

Thank you so much @TomMartens , This solution worked perfectly.

All the best.

Clk.