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:
[sourcecode language=”text” padlinenumbers=”true”]
Purchasing Customers:=
CONCATENATEX(
VALUES(Sales[Customer]),
Sales[Customer],
","
)
[/sourcecode]
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…
Thank you Chris! Always very interesting and clear insights!
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 🙂
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
Using this measure as the 1st argument of a CALCULATE function looks very powerful. As an individual, don’t have access to the preview, though. 🙁
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.
So is your preview part of your O365 subscription, or part of your professional relationship with MS?
It’s the public preview available to O365 for Business customers
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
Thanks!
anyone know where i might find a demo/blog of the power query method outlined by R Tockner?
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?
Yes, I guess you could use the TopN() function to get just the top 3 customers.
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]””),
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??