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:


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

Purchasing Customers:=

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


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

24 responses

  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. Pingback: Get selected items in a slicer in Excel 2016 | Erik Svensen

  4. 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!

    • Hi Matt, I genuinely don’t know when there will be a public beta – I have an Office 365 E4 subscription. I’m running the Preview on a VM so I don’t have to worry about it causing problems with other Office installations.

  5. A solution in in power query:

    Use the function Combiner.CombineTextByDelimiter and Table.Group


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




  6. Pingback: Excel Roundup 20150330 « Contextures Blog

  7. Pingback: Documentation For New Excel 2016 DAX Functions | Chris Webb's BI Blog

  8. Pingback: Documentation For New Excel 2016 DAX Functions - SQL Server - SQL Server - Toad World

  9. Pingback: Spreadsheet Roundup 20150408 – Spreadsheet Day

  10. 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?

  11. 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:=
      FILTER(VALUES(Sales[Customer]), Sales[Customer]””),

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

      • this approach seemed to also solve another problem I had which was I was wanting to eliminate duplicates
        btw assumed this line was actually FILTER(VALUES(Sales[Customer]), Sales[Customer]””),

  12. Pingback: Dynamic Chart Titles In Power BI – Chris Webb's BI Blog

  13. Pingback: Converting Lists Of Numbers To Text Ranges In Power Query – Chris Webb's BI Blog

  14. How do you make this work, if you are using multiple tables? Do you need to merge the tables in order for it to work. For example, i have 3 tables, one that uses the key for a person and the key for the type of transaction. Each day a person can only have one transaction type. I want to know the list of people with the same transaction code (word version) for each date. But each time I use this DAX calculation all I get is a complete list of EVERY SINGLE PERSON for EVERY SINGLE TRANSACTION CODE for every single date. Are there changes that are required??

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: