Tutorials

CONCATENATEX (DAX – Power Pivot, Power BI)

This article is about the CONCATENATEX function, which merges text strings in DAX. You can use it in measures (in contrary to CONCATENATE, also available in DAX, which can be only used for merging of columns together). If you wanted to merge text strings “conditionally” in classical Excel formula, you can use a matrix function.

How does it work?

Let´ s play with this table and merge all comments belonging to products together.

Let´s create this measure:

All comments = CONCATENATE(Table1;Table1[Customer comment];”; “)

Arguments are:

table to be used,
the column from it, that will be merged,
the string or character that will be used as separator.

After using the measure in visual (Matrix, for example…), and splitting by product, we can see the result – merged comments.

To see unique values as a result of CONCATENATEX see this article.

2 Comments

jacek says:

25.9.2019 at 6:22

hi
nice function
but i have issue. is it possible to merge only distinc values or sort this list?
thx
regards
jacek
Jiří Beran says:

17.10.2019 at 11:28

You can sort normally in CONCATENATEX – using the fourth and fifth optional parameter.
To get the unique values you need something like this:
List of unique values = CALCULATE(CONCATENATEX(DISTINCT(‘table'[Column]);’table'[Column]))

This article is about the CONCATENATEX function, which merges text strings in DAX. You can use it in measures (in contrary to CONCATENATE, also available in DAX, which can be only used for merging of columns together). If you wanted to merge text strings "conditionally" in classical Excel formula, you can use a matrix function.