ConcatenateX() DAX Function In Excel 2016

This is the first of many posts on the new DAX functions that have appeared in Excel 2016 (for a full list see this post). Today: the ConcatenateX() function.

The mdschema_functions schema rowset gives the following description of this function:

Evaluates expression for each row on the table, then return the concatenation of those values in a single string result, separated by the specified delimiter

Its signature is:

CONCATENATEX(Table, Expression, [Delimiter])

It’s easier to understand what it does using a simple example though. Consider the following table on a worksheet in Excel 2016:

image

When you add this table to the Excel Data Model (I called the table Sales) you can add the following measure:

Purchasing Customers:=
CONCATENATEX(
	VALUES(Sales[Customer]), 
	Sales[Customer], 
	","
)

If you then use this measure in a PivotTable, you see the following:

image

As you can see, the measure returns a comma-delimited list of all of the customers who have bought each product. Very useful…

21 thoughts on “ConcatenateX() DAX Function In Excel 2016

  1. I think there are quite some useful scenarios here this can be very handy.
    you can use it with any (calculated) table and also can set the Delimiter on your own to combine it e.g. with the PATH-functions
    I’m sure I will use this sooner or later in the future 🙂

  2. love it, any idea when this will make its way into the tabular instance. Would be really useful for identifying the other side of general ledger transactions without using earlier to scan through a potentially huge table

  3. I think this will be great as an addiction to VALUES that can return a single text value into a measure. Now if there is more than one value, you can concatenate them together and still put them in a cell. Very useful.

    Chris, do you know when the public beta will be out? How are you handling version control here? Do you have a VM or separate PC to run Office 16!

  4. A solution in in power query:

    Use the function Combiner.CombineTextByDelimiter and Table.Group

    *************************************************
    let

    Quelle = Excel.CurrentWorkbook(){[Name=”Tabelle1″]}[Content],
    fCombine = Combiner.CombineTextByDelimiter(“;”),
    #”Gruppierte Zeilen” = Table.Group(Quelle, {“Product”}, {{“Customer”, each fCombine([Customer]), type text}})
    in
    #”Gruppierte Zeilen”

    ****************************

    Greetings

    Reinhard

  5. Great post Chris! I really like CONCATENATEX, especially when working with ERP data. Do you know if there is a way to modify your measure expression so that it would return only the first 3 customers and, if there were more than 3, it would return “Too many” or something like that?

  6. Thank you Chris. This is what I was looking for. I am trying to create a measure for analyst comment file. I maintain a rolling forecast file where analysts put their comments on significant variance on each Cost center which roll into higher account, service and business group. My measure counts null cell (as I use zero length string “” with if condition for cells which does not have comments) and concatenates delimiters . How do filter out null cell or replace “” with blank?
    In excel 2016, what does 3 and 4th optional arguments means (order by expression and order1..)? I will appreciate an example. Thanks

    • Do you want to avoid concatenating the empty strings? If so, you’d need to do something like use the Filter() function in the first parameter:

      Purchasing Customers:=
      CONCATENATEX(
      FILTER(VALUES(Sales[Customer]), Sales[Customer]””),
      Sales[Customer],
      “,”
      )

      The second and third parameters control the order that the items appear in the concatenated list.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s