ConcatenateX() DAX Function In Excel 2016

Reposted from Chris Webb's blog with the author's permission.

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:= CONCATENATEX( 	VALUES(Sales[Customer]),  	Sales[Customer],  	"," ) 
 

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.

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com/ .