CONCATENATEX function

The CONCATENATE function is used to join strings in the DAX language. DAX also has a corresponding “X” function that acts as an iterator and allows joining of multiple strings with an option to use delimiter and sorting direction by given expression. In this post, you will learn how to use the CONCATENATEX function in a specific example, by displaying all brands in relation to the filter of the manufacturer to which they belong.

The syntax of the function is:

CONCATENATEX(<table>, <expression>,
[<delimiter>],[<order by expression>],[<order by>]) 

The first argument is a table. Here we can also specify the desired filter function or another one that returns the table. For example, by using the VALUES function we can get the unique values ​​of a given column. The second argument is an expression. As an expression we usually add a column name which contains necessary data. The third, optional argument, is a separator. It can be any character but usually it’s: blank, comma, semicolon, etc. Further we can add sorting expression and direction (ASC, DESC) with two last optional arguments.

How to use the CONCATENATEX function? We created a Power Pivot report showing the sum of quantities sold in relation to manufacturers (PROIZVOĐAČ). One manufacturer can have none, one or more brands (ROBNA MARKA). If we want to avoid creating a hierarchical view by manufacturer and brand, we can do so by adding a column in which all brands will be listed. To perform this action we should enter the formula:

Nazivi brendova:= IF(HASONEVALUE(Artikli[PROIZVOĐAČ]),
CONCATENATEX(VALUES(Artikli[ROBNA MARKA]),Artikli[ROBNA MARKA],”,”,
Artikli[ROBNA MARKA],ASC),BLANK())

In this formula, the CONCATENATEX function takes the unique values ​​of the brands, given as the first argument, the obtained values ​​are separated by a comma, and then sorted in ascending order.