Tutorials

CONCATENATEX providing unique (distinct) values

The CONCATENATEX function concatenates texts in measures / in visuals. But how to use it to show unique values?

Let´s have the table with cars, containing Brand and Model.

We need to know, which model of which brand is there.

If we simply used CONCATENATEX to create a measure and if we used this measure in a matrix, we would see this:

Models (like Spider) are not unique. So let´s modify the formula this way:

List of unique values = CALCULATE(CONCATENATEX(DISTINCT('table'[Model]);'table'[Model];", "))

Now it is much better - each model only appears once.

Tanuj says:

6.5.2020 at 11:19

any ideas on combining values in 2 columns trough a measure.
Jiří Beran says:

6.5.2020 at 12:01

Maybe something like sumx, countx, concatenatex? What exactly do you need?
Charu Ambekar says:

26.6.2020 at 14:49

Does the function VALUES work better than DISTINCT here?
arioke says:

3.9.2020 at 12:48

hi thanks dude for your help.
It works perfectly into my cross table DAX
Ruchi says:

21.10.2020 at 11:49

Awesome . It helped me. keep doing great work

The CONCATENATEX function concatenates texts in measures / in visuals. But how to use it to show unique values?

Let´s have the table with cars, containing Brand and Model.

We need to know, which model of which brand is there.

If we simply used CONCATENATEX to create a measure and if we used this measure in a matrix, we would see this:

Models (like Spider) are not unique. So let´s modify the formula this way:

List of unique values = CALCULATE(CONCATENATEX(DISTINCT('table'[Model]);'table'[Model];", "))

Now it is much better - each model only appears once.