Problem
In data analysis, there are several reasons why one would want to combine (join) two or more text strings in a dataset into a single text string. This can be to cleanse the data, or to create a unique column for data modelling purposes, or for other related reasons. This tutorial aims to describe some of the different ways to achieve this in Power BI using DAX.
Solution
Let’s take the table below to demonstrate a typical case where you might need to join/combine two or more text strings (columns) in a single column (text string).
For this demo we just need to join the “CalendarMonthName” column and the “CalendarYear” column to get a new column with values such as in the format “Jun 2013”.
There are various ways to approach this while using DAX in Power BI including
- Using the CONCATENATE function
- Using the COMBINEVALUES function
- Using the ampersand (&) operator
The above three approaches are the most common ways to handle this sort of tasks, but we can also handle similar tasks using the DAX CONCATENATEX function, this will be discussed in more detail in another article. I will discuss more about each approach next.
Using the DAX CONCATENATE function
The CONCATENATE function in DAX joins two text strings into a single text string. The DAX syntax for the CONCATENATE function is as shown below.
CONCATENATE(<text1>, <text2>)
The CONCATENATE function can only accept two arguments as seen in the syntax above. The arguments can be texts, numbers, Boolean as texts or combination of all, as well as using of column reference. Without going into details on the function itself, let’s see some examples of how you can use this function to join or combine text strings in DAX.
Using the table dataset provided earlier, lets combine the “CalendarMonthName” column and the “CalendarYear” column to get a new column as seen below.
With_CONCATENATE = CONCATENATE(DemoData[CalendarMonthName], DemoData[CalendarYear])
The output in the table is as shown below.
But as you can see, there is no space between the “CalendarMonthName” column and the “CalendarYear” column. This is because the CONCATENATE function only allows two arguments. Thus, we could not include a space as an argument in the formula above. The above example used a column reference combination, and in this case, it was not the best approach to use just the CONCATENATE function on its own.
There is a simple way to work around this by using nested CONCATENATE functions as seen in the DAX codes examples below.
With_CONCATENATE_V2 = CONCATENATE(DemoData[CalendarMonthName], CONCATENATE(" ", DemoData[CalendarYear]))
The new output of the table is as seen below.
You could also write the nested CONCATENATE as seen in the example below to by-pass the two arguments limitation of the CONCATENATE function in DAX on its own.
With_CONCATENATE_V3 = CONCATENATE (
CONCATENATE (
DemoData[CalendarMonthName],
" "
),
DemoData[CalendarYear]
)
There are several scenarios where the CONCATENATE function is used that is different from the example we have seen in this article, but for the purpose of this article I have not described these in more detail. I will look to write another article looking at the CONCATENATE function in DAX in more detail.
Using the DAX COMBINEVALUES function
The COMBINEVALUES function in DAX joins two or more text strings into a single text string. Although, the primary purpose of the COMBINEVALUES function is to support multi-column relationships in Direct Query models, but it is usually commonly used to do joins/combination of text strings or columns in DAX.
The syntax of the COMBINEVALUES function is as seen below.
COMBINEVALUES(<delimiter>, <expression>, <expression>[, <expression>]…)
The COMBINEVALUES function can accept two or more expressions as arguments unlike the CONCATENATE function. It must also contain a constant delimiter value which serves as the separator used during concatenation.
Using the table dataset provided earlier, lets combine the “CalendarMonthName” column and the “CalendarYear” column to get a new column as seen below.
With_COMBINEVALUES = COMBINEVALUES(" ", DemoData[CalendarMonthName], DemoData[CalendarYear])
As can be seen in the DAX code, I have used a space delimiter wrapped in a quote (I could have used any other delimiter too like comma (,) or pipe (|)). In this case, I have only joined two columns, but as seen in the syntax above, we can use more than two columns, making the COMBINEVALUES function a very flexible approach to use.
The output of the table is as seen below.
Using the ampersand (&) operator
The Ampersand (&) operator in Power BI using DAX is very similar to how it is used in Excel. It is used to join two or more text strings into a single text string. Also, unlike the CONCATENATE function, the Ampersand (&) operator can accept more than two arguments and like the CONCATENATE function it can the arguments can be texts, numbers, Boolean as texts or combination of all, as well as using of column reference.
There is no specific syntax to demonstrate this approach, but we can show examples of how it can be used to join/combine columns to create a single column in DAX. Using the dataset, we have been using in this demo, lets combine the “CalendarMonthName” column and the “CalendarYear” column to get a new column as seen below.
With_Ampersand (&) = DemoData[CalendarMonthName]& " " &DemoData[CalendarYear]
The Dax formula along with the output table is as seen below. Note that there are three arguments in the DAX formula used in this example - the “CalendarMonthName” column, the space delimiter enclosed in double quotes, and the “CalendarYear” column.
In summary, the CONCATENATE and the COMBINEVALUES DAX functions are two powerful DAX functions that can be used in joining or combining text strings in more complex ways than what have been described in this article. To better understand more details about how these DAX functions work and in what scenarios they can be used, a more dedicated article would be written on them separately.
Next Steps
