Day 2 : CONCATENATEX

 

ConcatenateX: A Powerful Tool for Dynamic String Concatenation in Power BI DAX

Understanding ConcatenateX

In Power BI DAX, the ConcatenateX function provides a flexible and efficient way to concatenate text values from a table. Unlike the simpler CONCATENATE function, which combines a fixed number of text values, ConcatenateX iterates over a table, concatenating values based on specified criteria.

Syntax:

CONCATENATEX( <table>, <expression>, <delimiter> )

 

• <table>: The table containing the values to be concatenated.
• <expression>: An expression that returns the text values to be concatenated for each row in the table.
• <delimiter>: The character or string that separates the concatenated values.

3 Practical Examples

1) Creating a comma-separated list of products:

ProductsList = CONCATENATEX(Products, Products[ProductName], ", ")

This formula iterates over the Products table, concatenating the ProductName values with a comma and space as a delimiter. The result is a single text string containing all product names separated by commas.

2) Building a custom customer information string:

CustomerInfo = CONCATENATEX(Customers, Customers[FirstName] & " " & Customers[LastName] & " (" & Customers[City] & ")", "; ")

This formula combines the customer's first name, last name, city, and adds parentheses around the city. The resulting string is separated by semicolons.

3) Dynamically creating a list of values based on a filter:

FilteredList = CONCATENATEX(FILTER(Products, Products[Category] = "Electronics"), Products[ProductName], ", ")

This formula first filters the Products table to only include products in the "Electronics" category. Then, it concatenates the ProductName values of the filtered rows.

Key Points to Remember

• ConcatenateX is particularly useful when you need to dynamically concatenate values based on conditions or calculations.
• The delimiter parameter allows you to customize the separator between concatenated values.
• You can use expressions within the <expression> argument to perform calculations or formatting on the values before concatenation.
• The X indicates its an Iterator function and therefore works at at the row level.

https://dax.guide/concatenatex/