CONCATENATEX function is a Power BI text function in DAX, which evaluates expression for each row on the table, then return the concatenation of those values in a single string result, separated by the specified delimiter.
DAX SYNTAX
CONCATENATEX(<table>, <expression>, [delimiter])
table is a table containing the rows for which the expression will be evaluated.
expression is expression to be evaluated for each row of the table.
delimiter a separator to use during concatenation. It is an optional.
Lets look at an example of using CONCATENATEX function in Power Bi, Here we sample data that represent the Total Quantity by Category and Subcategory.
As you can see, for any category there are multiple sub categories. If you wish to see all the subcategories realted to any category into single string, separated by the specified delimiter then you can do this using CONCATENATEX function.
Lets create a measure- SubCategoryList which uses CONCATENATEX function which concatenates the subcategories into single string, by spearating comma (,).
SubCategoryList = CONCATENATEX ( VALUES ( 'Global-Superstore'[Sub-Category] ), 'Global-Superstore'[Sub-Category], ", " )
Once you commit the DAX, Lets drag this on table visual, and you can see for each category all subcategories are concatenated into single string lists, separated by comma.
If you notice, measure is retuning all the unique subcategories for all categories in TOTAL also.
If you do not want to display it on TOTAL, then you remove it by modifying DAX as shown below.
SubCategoryList =
IF (HASONEVALUE('Global-Superstore'[Category]),
CONCATENATEX (
VALUES ( 'Global-Superstore'[Sub-Category] ),
'Global-Superstore'[Sub-Category],
", "
), "")
Now you can see, all concatenated subcategories string are removed from TOTAL
You can also add multiple expression in CONCATENATEX function, suppose you want to add quantity for each subcategory in string.
Lets modify the DAX, to display quantity along with each subcategory in string.
SubCategoryList =
IF (HASONEVALUE('Global-Superstore'[Category]),
CONCATENATEX (
VALUES ( 'Global-Superstore'[Sub-Category] ),
'Global-Superstore'[Sub-Category] & " :" & SUM('Global-Superstore'[Quantity]),
", "
), "")
