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…

22 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!

  5. 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

  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:=
      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.

      • 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

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s

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

%d bloggers like this: