CONCATENATEX is a DAX function introduced in Power Pivot for Excel 2016. It wont work in Excel 2013 or 2010
Here is the Syntax
CONCATENATEX (Table,Expression,[Delimiter],[Orderby…Expression1],[Order1],…)
Only the first 2 arguments are mandatory, so for the sake of understanding this formula lets simplify it
CONCATENATEX (Table,Expression)
As the name (letter X) implies, CONCATENATEX is an iterator. Like all iterators, this function
- first creates a ROW CONTEXT on the TABLE it receives as first ARGUMENT
- then processes the EXPRESSION within this ROW CONTEXT i.e. Concatenates the result of expression evaluated for each individual row of the Table
SOME ILLUSTRATIVE EXAMPLES
MICROSOFT Developer Network https://msdn.microsoft.com/en-us/library/mt163697.aspx gives the following example
| FirstName | LastName |
|---|---|
| Alan | Brewer |
| Michael | Blythe |
CONCATENATEX(Employees, [FirstName] & “ “ & [LastName], “,”)
Returns “Alan Brewer, Michael Blythe”
Lets look at a slightly advanced use of CONCATENATEX.
Click here to Download the illustrative example. In this example we have data model of two tables. The DATA TABLE contains the Earnings of Top 10 Highest paid athletes for each year from 2009 to 2013 prepared by Sports Illustrated. Many players as you can see have appeared multiple times in this list . For example Tiger Woods has been there in each of the 5 years. The second table “ATHLETES TABLE” or “LOOKUP TABLE” contains information regarding Sport and Country of the Athlete. The two tables and the relationship between them is depicted below
Our goal is to create a calculated column in “LookUp” Table which shows against each athlete the years in which he has appeared in this prestigious list. The picture below shows the desired output
TigerWoods , LeBron James ,Phil Mickelson have appeared in the list for consecutive 5 years
As you can see this column is quite useful. The CONCATENATEX formula that will give us these results is as follows
=CONCATENATEX (filter(Data,Data[Name]=LookUp[Name]) , Data[Year] , “,” ,Data[Year] , ASC )
Its well worth looking at this formula
We are using one iterator inside another iterator (FILTER INSIDE CONCATENATEX)
For each ROW in the LOOKUP table, we need a corresponding FILTERED DATA TABLE which contain only 1 athlete. This is achieved by using filter(Data,Data[Name]=LookUp[Name])
CONCATENATEX iterates over the FILTERED Data Table (1 athlete) and concatenates the values in the Data[Year] column of this filtered table using the DELIMITER “,”
This process is repeated for every athlete in the LookUp Table
I am already thinking of so many interesting uses of this function. If you have a creative use of this function, please share with me