It happens often in Power BI calculations and reports that you need to concatenate a list of values from a column. You can do this concatenation in Power Query or DAX. However, if the concatenation needs to be done dynamically. ConcatenateX is a very helpful DAX function to achieve such results. It is very helpful to understand what happens in the virtual tables in DAX too. In this article and video, I’ll explain what ConcatenateX is and how it works in Power BI and DAX.
Video
Sample dataset
I have a sample model like below with three tables;
ConcatenateX
Whenever you want to concatenate values from a column (either a real column from a real table or a virtual table) then ConcatenateX is a useful DAX function for it. ConcatenateX is a scalar function (it means it returns a scalar value), but it needs a table as one of the inputs parameters.
ConcatenateX(<table>, <expression>[ , <delimiter> [, <order by expression> [, <order> ]] ...])
the parameters are as below;
- Table: this can be a table in the model, or a virtual table created by tabular functions, or coming from a variable
- Expression: What expression from the given table should be concatenated
- (Optional) Delimiter: The delimiter to use when concatenating values
- (Optional) Order by expression: The expression that determines in what order the records of the table should be placed before concatenating
- (Optional) Order: The ASC or DESC order
The best way to understand how a function in DAX works is to go through some examples with it.
Some samples: Orders for each customer
Let’s start this by adding a column into the DimCustomer table. In this column, we want to see the SalesOrderNumber for each of the orders customers made. You can achieve it using the expression below;
Orders =
CONCATENATEX(
RELATEDTABLE(FactInternetSales),
[SalesOrderNumber]
)
The expression above should be added as a COLUMN to the DimCustomer;
The expression above is using the RelatedTable function to get all the rows from the FactInternetSales table based on the existing relationship to the DimCustomer, to learn more about the RelatedTable function, read my article here.
In the expression above, the RelatedTable(FactInternetSales) is used as the input table parameter and the SalesOrderNumber as the expression.
The expression is concatenating the SalesOderNumber of all the orders belonging to the given customer. However because the results are concatenated without a delimiter, it is a bit hard to read. We can add a delimiter like this:
Orders =
CONCATENATEX(
RELATEDTABLE(FactInternetSales),
[SalesOrderNumber],
","
)
The “,” is used as a delimiter and makes it much easier to read now.
You can use anything you want as the delimiter as a string.
The result shows some of the order numbers were duplicated. that is because one SalesOrderNumber might have multiple order lines in it. You can either summarize the table and get only the order numbers, or you can bring orderLineNumber into the concatenation too.
Orders =
CONCATENATEX(
RELATEDTABLE(FactInternetSales),
[SalesOrderNumber]&"-"&[SalesOrderLineNumber],
","
)
In the expression above, we have concatenated the SalesOrderNumber and the SalesOrderLineNumber. This is the value that will be first generated in each row of the given table and then concatenated in the final result.
Let’s say you want to get the result ordered by SalesAmount itself. Then you can add a fourth parameter for order expression. And you can choose the order of ascending or descending as the fifth parameter as well.
Orders =
CONCATENATEX(
RELATEDTABLE(FactInternetSales),
[SalesOrderNumber]&"-"&[SalesOrderLineNumber],
",",
[SalesAmount],
DESC
)
This will give you the result, but this time, the orders with higher sales appear first;
ConcatenateX as a Measure
Although, you can use ConcatenateX to create a calculated column (as you have seen in the above examples). However, this can be also achieved in Power Query too. In fact, if you are pre-concatenating values, it might be better to do it in Power Query because you can use it as a dataflow and leverage the results of it in multiple Power BI datasets. In this article I explained how to pre-concatenate values in Power Query;
The most common and effective usage of the ConcatenateX comes when we use it as a Measure. Because in that case, the concatenation happens based on the selection of other filters in the report. Let’s go through the example below;
List of Top Customers in each category
The requirement is to show the top 10 customers who purchased products in each color category in the given date frame as below;
This can be done of course by adding another chart or drill-through, or even a report page tooltip. However, let’s assume that the requirement is to show everything in one table like the above screenshot.
As you see, in the screenshot above, we have concatenated list of customer names and their Sales. This is grouped by each product color, and most importantly, there is a filter on the OrderDate that should affect the list of customers. This means the calculation has to be done as a measure rather than a column.
In order to achieve the result, I used some variables. This helps you to understand the process step by step. The first variable gives me the Customer table but with a new column added to it as Sales. To learn more about the AddColumns function used in this expression, read my article here;
var customersWithSales=ADDCOLUMNS(
DimCustomer,
"Sales",
SUMX(
RELATEDTABLE(FactInternetSales),
FactInternetSales[SalesAmount]
)
)
Then we can filter it to get only the customers who have purchased something, in other words, their Sales amount is not zero. To learn more about the Filter function, read my article here.
var customersWhoPurchased=FILTER(
customersWithSales,
[Sales]>0)
Now, we can filter the customers and get only the top 10 customers based on their Sales. To learn more bout the TopN function, read my article here.
var TopCustomers=TOPN(10,customersWhoPurchased,[Sales],DESC,[FullName])
Or we can get the bottom 10 customers like below;
var BottomCustomers=TOPN(10,customersWhoPurchased,[Sales],ASC,[FullName])
That is pretty much now ready to be used for concatenation. Using the results above and with some help of the Format function in DAX, we can get the outcome;
CONCATENATEX(
BottomCustomers,
[FullName]&": "&FORMAT([Sales],"$0,0.00"),
", ",
[Sales],
DESC
)
The whole expression of the measure is as below;
Customers who purchased this product =
var customersWithSales=ADDCOLUMNS(
DimCustomer,
"Sales",
SUMX(
RELATEDTABLE(FactInternetSales),
FactInternetSales[SalesAmount]
)
)
var customersWhoPurchased=FILTER(
customersWithSales,
[Sales]>0)
var TopCustomers=TOPN(10,customersWhoPurchased,[Sales],DESC,[FullName])
var BottomCustomers=TOPN(10,customersWhoPurchased,[Sales],ASC,[FullName])
return
CONCATENATEX(
BottomCustomers,
[FullName]&": "&FORMAT([Sales],"$0,0.00"),
", ",
[Sales],
DESC
)
The expression above returns the concatenated list of the bottom 10 customers, However, you can change the first parameter of the ConcatenateX to the TopCustomers variable and get the top 10 customers instead.
As the expression is written as a measure, the result changes dynamically based on the selection of the OrderDate;
ConcatenateX for Debugging Virtual Tables
Another very useful and common way of using ConcatenateX is to debug virtual tables. That is when you want to see some of the values in the virtual table created inside a measure to see if you are on the right track or not. I explained that in full detail in this article;
Summary
ConcatenateX is a very useful function in DAX when you want to concatenate the values of a column. However, remember if you are pre-concatenating values, then Power Query might be a better option. ConcatenateX used as a measure can be a powerful way to debug virtual tables too. I hope this article helps you to learn more about this function. Let me know about your experience with ConcatenateX in the comments below.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.