DAX CONCATENATE Function

The DAX CONCATENATE function in Power BI is used to join two text strings into a single text string. This function is useful for combining text from different columns or creating custom labels and strings for your reports and dashboards.

Syntax:

CONCATENATE(<text1>, <text2>)

<text1>: The first text string to join. <text2>: The second text string to join.

Purpose:

The CONCATENATE function allows you to merge text from different sources into one cohesive string. This can be particularly useful for creating descriptive labels, combining names, or formatting text-based outputs.

Example:

Suppose you have a table named "Employees" with columns "FirstName" and "LastName". You want to create a new column that combines these two columns into a single column named "FullName".

You can use the CONCATENATE function as follows:

FullName = CONCATENATE(Employees[FirstName], Employees[LastName])

Example Scenario:

Assume you have the following "John" table:

Using the CONCATENATE function, you can combine the first and last names:

FullName = CONCATENATE(Employees[FirstName], " " & Employees[LastName])

The resulting table would be:

Combining with Other Functions:

You can combine CONCATENATE with other text functions to format your strings better. For instance, using the & operator for more complex concatenations.

FullName = Employees[FirstName] & " " & Employees[LastName]

Handling Null Values:

Be aware that if any of the text values being concatenated are blank, the resulting string will still concatenate but might not display as intended.

Example with Additional Formatting:

To create a full name with a comma between last name and first name:

FormattedFullName = Employees[LastName] & ", " & Employees[FirstName]

Related Functions:

CONCATENATEX: Concatenates the result of an expression evaluated for each row in a table, using a specified delimiter.

LEFT: Returns the specified number of characters from the start of a text string.

RIGHT: Returns the specified number of characters from the end of a text string.

MID: Returns a specific number of characters from a text string, starting at the position you specify.

UPPER: Converts a text string to all uppercase letters.

LOWER: Converts a text string to all lowercase letters.

TRIM: Removes all spaces from a text string except for single spaces between words.

Example with CONCATENATEX:

To concatenate values from a column with a delimiter, use CONCATENATEX:

ConcatenatedNames = CONCATENATEX(Employees, Employees[FirstName] & " " & Employees[LastName], ", ")

This expression would create a single text string of all full names, separated by commas.

The CONCATENATE function in Power BI is a straightforward yet powerful tool for joining text strings. Understanding how to use CONCATENATE and the & operator effectively allows you to create meaningful and well-formatted text outputs in your data models and reports.