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.

  • Now supported in http://www.daxformatter.com too!

  • vinneyk

    I was glad to find this function… until I realized you can’t pass in an array as the second and final argument 🙁

    • Try CONCATENATEX 🙂

      • vinneyk

        Oddly, it doesn’t appear to be a valid option in my Power BI Measure. I’m using the Jan 2018 version.

        • Can you share the full measure formula you are trying to write?

  • comish4lif

    Is there a function, or a way to use this function that will skip blank expressions? This is, I wouldn’t want the concatentaion result to begin with the delimiter (if the first expression is blank or null). I also wouldn’t want the result to end with the delimiter if the final expression is blank or null. Nor would I want the result to show consecutive delimiters if consecutice expressions are both blank or null.

    My use case is that I have a group of fields that provide an “A” when true and ultimately the order of the “A” flags is not imopprtant – it is important to note how many flags are set. So, I’d rather not see “–AA” or “AA-” or “A–A” – the preferred output there would all be “AA”.

    • It sounds like you don’t care about delimiters at all — in this case, you could wrap your COMBINEVALUES in SUBSTITUTE and remove any delimiters.