Forum Discussion

naveedsaeed1's avatar
naveedsaeed1
New Member
3 years ago

DAX command with condition

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 

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    You could use

    My Measure =
CONCATENATEX (
    DISTINCT (
        FILTER ( ALLSELECTED ( Table1[Mode] ), NOT ISBLANK ( Table1[Mode] ) )
    ),
    Table1[Mode],
    ", "
)
  • naveedsaeed1's avatar
    naveedsaeed1
    New Member
    3 years ago

    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