There is a new DAX function coming soon to Power BI Desktop: COMBINEVALUES. It concatenates an unlimited number of values with a delimiter. You can already use the function by connecting to a dataset in Power BI service and creating report-level measures.
Existing ways to concatenate text strings
So far in DAX, there have been two functions for concatenation of text: CONCATENATE and CONCATENATEX.
CONCATENATE can piece together any two strings. If you wanted to concatenate more than two strings, you had to either nest CONCATENATE, or use ampersands like so:
ABC = "A" & "B" & "C"
In 2015, CONCATENATEX was added to DAX. As its X suffix implies, it iterates over a table and combines the result of an expression evaluated for each row, with an optional delimiter.
A new way to combine values
The new function, COMBINEVALUES, works like CONCATENATE, but it expects at least three arguments:
- First expression
- Second expression
- [optional nth expression]
The delimiter must be a constant non-empty string, meaning you cannot use “”, a variable, or a function as a delimiter. The expressions can be any kind of scalar values.
For example, you can join text strings “A” and “B” with a comma and a space between them like so:
A, B = COMBINEVALUES ( ", ", "A", "B" )
As mentioned above, you can join together an unlimited number of strings. You simply need to insert more arguments for that:
A and B and C and D = COMBINEVALUES ( " and ", "A", "B", "C", "D" )
Like AND and OR functions, COMBINEVALUES can improve the readability of your code.
In the label measure, Jordi combines five text strings:
Best product label = "The best sold product is " & [Top 1 Productname for Sales] & " with " & FORMAT([Sales for Top 1 productname],"€ #,#") &" in sales"
The measure’s readability can be improved with COMBINEVALUES:
Best product label = COMBINEVALUES ( " ", "The best sold product is", [Top 1 Productname for Sales], "with", FORMAT ( [Sales for Top 1 productname], "€ #,#" ), "in sales" )
This function can also be used calculated columns with composite keys.