How to use CONCATENATEX in Power BI (2 Examples)

by

In this article, we go into detail on the CONCATENATEX DAX function in Power BI and show you how to use it with multiple columns.

If you’re looking for a simple way to concatenate columns in Power BI, we have also covered this in a separate post.

What is the difference between CONCATENATE and CONCATENATEX in Power BI?

The difference between CONCATENATE and CONCATENATEX in Power BI is that CONCATENATEX has the ability to iterate over each row in a table and concatenate values based on certain conditions, while CONCATENATE simply concatenates a range of cells without any iteration.

There are five parameters in the CONCATENATEX function:

  1. Table
  2. Expression
  3. Delimeter
  4. Order by expression
  5. Order

The function works by iterating over each row in the specified table (first parameter) and evaluates the expression (second parameter) for each row.

It then concatenates the resulting text values into a single text string, with each value separated by an optional delimiter (third parameter).

The text strings can be ordered based on the criteria specified in the ‘order by’ expression (fourth parameter), either in ascending order or descending order (fifth parameter).

How to use CONCATENATEX with multiple columns

You can concatenate multiple columns with CONCATENATE by using the & and ” symbols.

The expression you use in your CONCATENATEX formula can use these symbols to to incorporate different columns from one table.

Here is an example using multiple columns:

Below is a table of data called ‘Product Sales’.

We have another table called ‘Product Costs’.

In this table, we want to show the number of orders each product has, as well as the product name, in one cell.

Here is the CONCATENATEX formula we would use to achieve this in our custom column:

Orders = CONCATENATEX(RELATEDTABLE('Product Sales'),'Product Costs'[Product]&": "&'Product Sales'[Orders],",")

The RELATEDTABLE function is used to allow the concatenation of the ‘Orders’ column from the ‘Product Sales’ table.

We use &” to concatenate the product name and product orders from both tables.

Here is the result:

Please note that to use multiple columns from different tables with CONCATENATEX and RELATEDTABLE, the tables must be joined with a relationship.

In our example, the Product Sales and Product Costs tables were joined with a 1:1 relationship, with the Product ID being the primary key.

How to order by with CONCATENATEX

CONCATENATEX in Power BI also allows you to order your expressions based on a certain criteria you specify.

These are the fourth and fifth parameters in the function.

The order can also be changed from ascending to descending if required.

Here is an example using CONCATENATEX and ordering the expressions.

Below is an example table called Orders:

Here is another table that has example Customer data in we will be using:

We want to create a column in this customer table that lists all of the customers’ orders (order numbers and values).

Since all of a customers orders will be in one cell, we also want to order them from highest value to lowest value.

Here is the CONCATENATEX formula we would use to achieve this custom column:

Orders = CONCATENATEX(RELATEDTABLE(Orders),"Order Number: "&Orders[Order Number]&", Value: $"&Orders[Amount]," and ",Orders[Amount],DESC)

Here is the result:

The last two parameters specify that the expression should be ordered by the amount in the orders table, from highest to lowest (descending).

Leave a Comment