New DAX function: COMBINEVALUES

The COMBINEVALUES function in DAX

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:

  1. Delimiter
  2. First expression
  3. Second expression
  4. [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" )

Use cases

Like AND and OR functions, COMBINEVALUES can improve the readability of your code.

Take, for example, the Top 1 per category quick measure by Jordi Frijters.

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.