Concatenatex related distinct values

[bugs84]

Hello everyone.

I have a measure in a table that gets the concatenation of values in a different table:

ListProductTypes = CONCATENATEX(Product;Product[ProductType];", ")

Work perfectly, but since I have many related Products (often with the same ProductType), I get an awful repetition of same values

e.c. Type1, Type1, Type2, Type2....

I only need to get (in this case) the distinct Type1, Type2

therefore i need something like ListProductTypes = CONCATENATEX(Product; DISTINCT ( Product[ProductType] ) ;", ")

but it doesn't work...

help would be appreciated

[bugs84]

ListProductTypes = CONCATENATEX(Product;Product[ProductType];", ")

Work perfectly, but since I have many related Products (often with the same ProductType), I get an awful repetition of same values

e.c. Type1, Type1, Type2, Type2....

I only need to get (in this case) the distinct Type1, Type2

therefore i need something like ListProductTypes = CONCATENATEX(Product; DISTINCT ( Product[ProductType] ) ;", ")

but it doesn't work...

help would be appreciated

[mahdi1985]

Hi everyone,

I have a table:

Col1           ,Col2

a 1
b 2
c 3
a 4
b 5
c 6
a 7
b 8
c 9
d 10
e null
f null
g null

i want to concatenate the column Col2 to get:

a 1,4,7
b 2,5,8
c 3,6,9
d 4,7,10
e
f
g

Here is the formula i used: «CONCATENATEX(Merge1,Merge1[Col2],"; ")»

The result i get is:

a 1,2,3,4,5,6,7,8,9,10,,,
b 1,2,3,4,5,6,7,8,9,10,,,
c 1,2,3,4,5,6,7,8,9,10,,,
d 1,2,3,4,5,6,7,8,9,10,,,
e 1,2,3,4,5,6,7,8,9,10,,,
f 1,2,3,4,5,6,7,8,9,10,,,
g 1,2,3,4,5,6,7,8,9,10,,,

i'm not sure if i missed something

Thank you for your help,

[zbpor]

Hi,

Sorround it with CALCULATE, it should works.

CALCULATE(CONCATENATEX(Merge1,Merge1[Col2],"; "))

[v-ljerr-msft]

Hi @bugs84,

Have you tried the solution provided by @Zubair_Muhammad above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

If you still have any question on this issue, feel free to post here. 

Regards

[Zubair_Muhammad · Accepted solution]

@bugs84

Try chnaging the location of DISTINCT

ListProductTypes = CONCATENATEX(DISTINCT(Product);Product[ProductType]  ;", ")

or

ListProductTypes = CONCATENATEX(VALUES(Product);Product[ProductType]  ;", ")

[HalloWorld]

This only works if the Product table has just one column. Else you need to write Product[ProductType] as the table so that you have only this one column to get the distinct values from:

ListProductTypes = CONCATENATEX(DISTINCT(Product[ProductType]);Product[ProductType]  ;", ")

See CONCATENATEX providing unique (distinct) values – Trainings, consultancy, tutorials.

[bugs84]

Thank you very much!

I tried the first solution and it works perfectly!