Power BI CONCATENATE Function: How and When to Use it

By: Kenneth A. Omorodion   |   Updated: 2021-11-03   |   Comments   |   Related: > Power BI

Problem

In DAX, there are two concatenating functions which are among the DAX functions that are used to join or concatenate strings together in Microsoft Power BI. These functions are the CONCATENATE and the CONCATENATEX functions. The main differences in both include how they are used along with some other factors to consider while using these functions.

In this article, we will look at the CONCATENATE function in more detail, and we will also demonstrate examples of common business cases where this function could be best used.

Solution

To better understand this function, it is better to start by describing the constituents of its syntax.

The DAX CONCATENATE function

The DAX CONCATENATE function joins/combines two strings into a single text string. While using this DAX function it is important to understand the following factors:

• If you are using row-level security (RLS) rules or need to create a calculated column in your data that depends on a Direct Query mode, the use of CONCATENATE function is currently not supported.
• Note that although the CONCATENATE function in Excel accepts up to 255 arguments, the DAX CONCATENATE function only accepts two arguments. Check my other article here on how you can overcome this limitation.
• Where you need to include text strings directly (instead of using column references) in the join, you should ensure you enclose each string in a double quotation. We will see examples in this article later.
• Last but not the least, the joined strings in the CONCATENATE function can be numbers, column references (where the column contains appropriate values), texts, Boolean values represented as texts, or a mixture of any of these.

Power BI DAX CONCATENATE Syntax

As the DAX CONCATENATE function only accepts two arguments, the syntax is very simple as seen below.

CONCATENATE(<text1>, <text2>)  

So, when and how do you use this DAX function? I have described some common use cases when you would usually need to use the CONCATENATE function in DAX below.

Concatenate Literals

By concatenate literals we mean that you need to combine two string values that you provide as arguments to create a single new string value. There are reasons to do this in Power BI development, including when you need to create a "Title" for your visual or report page using conditional formatting. See the example code I have created below.

Title 1 =
CONCATENATE ( "Transactions ", "Period" )

Note in the measure I created that I have deliberately left a space at the front of "Transactions" to ensure there is a space between the two arguments. A sample table visual I have used this "Title 1" on is as seen in the diagram below.

To use the concatenated measure as visual title you would need to configure it on the Title conditional formatting. Get more details on how to do this in this article.

Concatenate Strings in Columns

This refers to when you need to concatenate values in two columns of a dataset. Let’s use the dataset below for example, to combine the "First Name" and "Last Name" into a single column called "Full Names".

To do this we will create a calculated column as follows.

Full Names =
CONCATENATE (
    Sample_HR_Dataset_v2[First Name],
    CONCATENATE ( " ", Sample_HR_Dataset_v2[Last Name] )
)    

Note that I have used a Nested concatenate expression here. This is to help me eliminate the limitation of two arguments in the DAX CONCATENATE function. There are other ways to approach and eliminate this limitation, see my other article here on how this is done. See the table below showing the calculated column using CONCATENATE function.

What if we needed to create the "Full Name" column to include the "Middle Initial" column at the middle. This would result in additional argument in the DAX CONCATENATE formula. But we can again eliminate this limitation by being a little more creative. To do this we will use the DAX calculated column formula below.

Full Names v2 =
CONCATENATE (
    CONCATENATE (
        Sample_HR_Dataset_v2[First Name],
        CONCATENATE ( " ", Sample_HR_Dataset_v2[Middle Initial] )
    ),
    CONCATENATE ( " ", Sample_HR_Dataset_v2[Last Name] )
)    

The resulting calculated column showing the "Full Names v2" column is as shown below.

Conditional Concatenation of Strings in a Column

This relates to situations when you need to create a concatenated column based on a condition. I mean, for instance, let’s say we needed to create a column using the table below, the column should have the employee full names including the "Middle Initial". But where there is no "Middle Initial", we need to add a conditional concatenation to take the first letters of the "First Name".

To do this, we need to create a calculated column as shown below.

 Cond_Full Names =
CONCATENATE (
    Sample_HR_Dataset_v2[First Name] & " ",
    CONCATENATE (
        IF (
            LEN ( Sample_HR_Dataset_v2[Middle Initial] ) < 1,
            LEFT ( Sample_HR_Dataset_v2[First Name], 1 ) & ", ",
            Sample_HR_Dataset_v2[Middle Initial] & ", "
        ),
        Sample_HR_Dataset_v2[Last Name]
    )
)   

Again, note that I have used a Nested concatenate along with a combination with the Ampersand (&) operator. There are several use cases you can adapt this to in your Power BI development, but this article just aims to describe when and how to use it.

The resulting calculated column is as seen in the diagram below.

Concatenate columns in a dataset with different data types

This is a very common use case for CONCATENATE function. It can be a combination of concatenation of Literals and Strings in Columns or any other forms. Typical use cases are when you need to create a conditional dynamic visual headers or titles or, just to combine columns with different data types to form a unique column for creating relationships using bridge tables in Power BI data modelling.

For an example, I have created a measure using the dataset table below.

The measure code is as seen below.

 Title v2 =
CONCATENATE (
    CONCATENATE ( "Transactions ", "Period" ) & " " & " for ",
    SELECTEDVALUE ( DemoData[CalendarMonthName] )
) 

The above measure is to be used as a dynamic title for the table visual below. When a slicer value is selected in the Calendar Month Name slicer, the visual title displays the calendar month name concatenated with "Transactions Period".

The use cases for the DAX CONCATENATE function can be extensive, but for the purpose of this article and to make this brief enough I will not go beyond these examples provided.

In summary, as mentioned earlier, there are other use cases for the CONCATENATE DAX function which I have not mentioned in this tutorial. You can always follow the steps and approach used in this article to solve your particular use case, and please do drop in the comments section any other use case you have applied this DAX function for so others can learn. However, you can do some further research on this function using the links in next steps below.

Next Steps

• Checkout this Microsoft documentation on how to create expression-based titles in Power BI here.
• Get some more information on CONCATENATE function in this Microsoft documentation here.
• Try this tip out on your own data as business requires.

Learn more about Power BI in this 3 hour training course.

About the author

Kenneth A. Omorodion is a Business Intelligence Developer with over eight years of experience. He holds both a bachelor’s and master’s degree (Middlesex University in London). Kenneth has the MCSA, Microsoft Data Analyst - Power BI and Azure Fundamentals certifications. Kenneth is a Microsoft Certified Trainer and has delivered corporate training on Power BI, SQL Server, Excel and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips