topic Re: DAX command with condition in DAX Commands and Tips

DAX command with condition

naveedsaeed1 — Fri, 11 Aug 2023 07:08:03 GMT

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

Re: DAX command with condition

johnt75 — Fri, 11 Aug 2023 10:52:52 GMT

You could use

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

Re: DAX command with condition

naveedsaeed1 — Mon, 14 Aug 2023 07:02:50 GMT

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