Join two selected values of a column with "and" in the middle

[Luzadriana255]

Hello dear community,

I am using the following Formula to join with "and" two values of a column, if both of them are selected

Selected order= CONCATENATEX(

    VALUES('Type'[Order]),

    'Type'[Order],

    " and ",

    'Type'[Order],

    ASC

)

I get the following:

"and individual and groups"

I do not want "and" to appear in the beginning, I just want it to appear in the middle., like this:

"individual and groups"

Could you please help me find a way to correct this mistake? thank you!!

[tamerj1 · Accepted solution]

Hi @Luzadriana255 
Please try

Selected order =
CONCATENATEX (
    FILTER (
        VALUES ( 'Type'[Order] ),
        'Type'[Order] <> BLANK ()
            && 'Type'[Order] <> ""
    ),
    'Type'[Order],
    " and ",
    'Type'[Order], ASC
)

[Luzadriana255]

Thank you so much @tamerj1  it works perfectly!