DAX - Join multiple strings and ignore null or empty values

Question

I want to join multiple string values into a single string using DAX. I get the strings from several columns of a table.

Examples

"I", "", "" -> "I"

"", "got", "" -> "got"

"I", "got", "" -> "I, got"

"I", "", "rhythm" -> "I, rhythm"

The best I came up with is COMBINEVALUES(", ", string1, string2, string3 ...) but this does not ignore null or empty value. The output of the first example would be

"I, , "

The function has nothing similar to the ignore_empty parameter in Excel TEXTJOIN. Is there a simple way to achieve it?

Answer 1

We will use CONCATENATE  to join multiple string values into a single string using DAX

You need to use CONCATENATE, this is not combining values but joining text

CONCATENATE(<text1>, <text2>)

You can also use this one for join multiple string:

NewColumn = text1 & text2