DAX command with condition

[naveedsaeed1]

Hello community team,

I am using below ConcatenateX formula for the purpose to join multiple values in pivot if there is any dual values for the same variable.

=CONCATENATEX(DISTINCT(Table1[Mode]), Table1[Mode] ,", ")

Although there is one limitation is that whenever there is no second value or blank value so it still return a comma and a space. For Example: "ModeValue, " and sometimes if initial value is blank then it returns ", ModeValue"

I would like if there is no second value/ blank value or initial value is blank so I want the individual value to be returned without comma and the space. For Example: "ModeValue"

Thanks in advance 

[naveedsaeed1]

Dear John, Thanks for the input.

when i apply the same formula, there is a bit of change in the output as below. If you notice so, the values on the left are correct although when I add your formula in Excel, the output is uniform for all months (mentioned as TestFormula).

Thanks 

[johnt75]

You could use

My Measure =
CONCATENATEX (
    DISTINCT (
        FILTER ( ALLSELECTED ( Table1[Mode] ), NOT ISBLANK ( Table1[Mode] ) )
    ),
    Table1[Mode],
    ", "
)