Data Concatenation is one of the data transformation steps used for simplifying data analytics.

For data concatenation in Power BI, it is not necessary to create a separate column in your dataset for the concatenation. You can concatenate the data directly in the visuals as well.

In this blog post, we’ll see the difference between the 2 Power BI DAX functions used for data concatenation – CONCATENATE() and CONCATENATEX()

Let’s start with CONCATENATE().

CONCATENATE ()

While in excel the concatenate function accepts up to 255 input values, in DAX we are limited to only 2. This restriction can be overcome by using ‘&’ in between multiple values. 

Here’s the expression used for the CONCATENATE() function:

When used in the context of a new column being added to a data table, you are able to reference columns directly. The function uses the relative values as context within the row in order to complete the function.

Power BI Row Level Security (RLS)- Simplified Setup Guide >>

To use CONCATENATE within a measure, you need to embed each column reference into a SELECTVALUE() function in order to specify the row values within the context, as shown here:

This measure can only be used in a visual with specific context that returns only one row of data for the measure to calculate and return the relevant values.

What we want to see is a list of all Harry Potter characters concatenated with the form of their personal Patronus broken out by their Hogwarts House. So, let’s take the “Character Name (Patronus)” measure created in the above image and put it in a table with the “House” values. 

We get nothing returned aside from the “quoted” artifacts from the measure. The reason for this is the field “House” does not provide specific enough context to the visual to specify the values for the function to return a singular value.

Supercharge OBIEE, OAC and OAS With Power BI [eBook] >>

Now if we add Patronus to the table things start to improve:

There is more information returned this time, however there are a few rows that fail to return character names. 

It is apparent that the rows that returned both “Character name” and “Patronus” values are where one Character in a particular House had that specific Patronus.

The rows associated to a Patronus that belongs to more than one character in a House fail to return a “Character name” value. Both Harry and his father James have the same Patronus (a Stag), as a result the function is prevented from returning a Name value for this field.

Instead of including the Patronus field, lets use the Name field.

This is more like it. Because we have provided unique values representing individual rows of data, the context of this table is now capable of returning values for both parameters of the CONCATENATE() function.

These context limitations may be suitable for the specific solution you are developing, but what if you want to return one concatenated and delimited list of each Character (and their Patronus) for each house… without needing to provide row-level values in the table.

This is where CONCATENATEX() comes in handy.

CONCATENATEX()

As with other DAX functions (SUM, COUNT, AVERAGE,… CONCATENATE) when you add an “X” to the end it denotes that you are now using a table function.

Table functions like CONCATENATEX() iterate over each row in a table using the visual context as filters.

The CONCATENATEX() function requires two parameters: a table and an expression. There are also optional parameters: Delimiter, Order By, and Order.

Let’s use this function to create the same concatenation as our previous example. It will look something like this:

Now we will put this measure into a table visual with the House values (just like we did with the CONCATENATE() function):

As you can see, we immediately get values returned to the visual. By using the table function version of CONCATENATE, we are iterating over each row in the “HP Characters” table and collecting all of the values associated with the context in the table visual (House values).

The 5 Must-haves of Power BI OBIEE Connectors [Infographic] >>

If there is no context provided, then we just end up with a long string, a list of all the Name and Patronus values.

To make the list easier to read we can delimit (the 3^rd input parameter) using ‘UNICHAR(10)’ which returns a line break. Looking like this:

You can also use the Order by parameter to sort the results based on any column used within the expression, and order it using the expression 1/TRUE/ASC for ascending or 0/FALSE/DESC for descending.

Both CONCATENATE() and CONCATENATEX() can be extremely useful. Depending on your requirements, you now have the necessary information to be able to decide which one is more appropriate for your project.