Measure - how to CONCATENATEX DITINCT value in a measure

[ngct1112]

Hi, I would like to ask is there any way I could concatenate all distinct value for a measure.

In this example, "Product" is a MEASURE.

I hope I can get a result "A, B, C", using sth like

=Calculate(Concatenatex(Table,Distinct(Table[Product]),", ")

Thanks for any idea!

ID	Product(measure)
1	A
2	A
3	B
4	C
5	C

[thedatahiker · Accepted solution]

Hi @ngct1112 
I would try this. 

Create a new table within a variable and add your measure as a column in that table. Here is documentation on ADDCOLUMN: ADDCOLUMNS function (DAX) - DAX | Microsoft Docs

Return that table variable in a concatenatex statement and return the new column you created.

MeasureName = 
VAR TableVar = 
ADDCOLUMN ( Table, "NewColumnName", [Measure] )
RETURN
CONCATENATEX ( TableVar, [NewColumnName], "," )

[v-yalanwu-msft · Accepted solution]

Hi, @ngct1112 ;

You could try:

MeasureName = 
VAR _new = 
DISTINCT(SELECTCOLUMNS( 'Table', "New", [Measure] ))
RETURN
CONCATENATEX ( _new, [New], "," )

he final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ngct1112]

@v-yalanwu-msft , your measure somehow works perfectly in my model. Great thanks for that!

[thedatahiker · Accepted solution]

Hi @ngct1112 
I would try this. 

Create a new table within a variable and add your measure as a column in that table. Here is documentation on ADDCOLUMN: ADDCOLUMNS function (DAX) - DAX | Microsoft Docs

Return that table variable in a concatenatex statement and return the new column you created.

MeasureName = 
VAR TableVar = 
ADDCOLUMN ( Table, "NewColumnName", [Measure] )
RETURN
CONCATENATEX ( TableVar, [NewColumnName], "," )

[ngct1112]

@thedatahiker , With your provided solution, I am still getting "A,A,B,C,C". May I ask how I could only concate the distinct values?"A,B,C"

Great thanks again.

[Samarth_18]

Hi @ngct1112 ,

To get distinct values you can update your code as below:-

MeasureName =
VAR TableVar =
    SELECTCOLUMNS ( 'Table_product', "NewColumnName", [Product(measure)] )
RETURN
    CONCATENATEX ( DISTINCT ( TableVar ), [NewColumnName], "," )

Thanks,

Samarth