topic Concatenatex with Distinct column values in DAX Commands and Tips

Concatenatex with Distinct column values

loganwol — Mon, 14 Nov 2016 21:29:39 GMT

Hi,

I am summarizing data from one table into another. Part of the data in the original table is a column of type text which I generate a comma delimited summary string.

Issues =  
    var ft = FILTER(RELATEDTABLE(Data), 
                  [ID] = Data[ID] && Data[Result] <> "Pass" && 
                  Not ISBLANK(TRIM(Data[Issue]))) 
    var issuessummary = CONCATENATEX(ft, Data[Issue], ",")
return 
     issuessummary

This works perfectly but I realized that the return values can be duplicate, so a result value string for issuessummary could equal "ABC", "DEF", "ABC", "xyz". I would like to remove the duplicate in the return string to represent "ABC", "DEF", "xyz".

Is there a way to accomplish that?

Regards,
Sunil

Re: Concatenatex with Distinct column values

bugs84 — Fri, 12 Jan 2018 12:49:58 GMT

Hi all,

I'm looking for the same...

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

how can I get only distinct ProductType values?

Re: Concatenatex with Distinct column values

markdy — Wed, 17 Jan 2018 19:04:03 GMT

Hey There bugs84,

I think this might help:

ListProductTypes = CALCULATE(CONCATENATEX(VALUES('Table'[ProductType]),'Table'[ProductType],","))

Credit Goes to Data and Analytics with Dustin Ryan:

https://sqldusty.com/2016/06/29/5-more-power-bi-tips/

According to Dusting Ryan:

Cited References:

Dustin, Ryan. 5 More PowerBI Tips. Data and Analytics with Dustin Ryan, 2016. Web. 17 January 2018.

Re: Concatenatex with Distinct column values

zapppsr — Wed, 11 Jul 2018 14:11:28 GMT

Very good, thanks!

Re: Concatenatex with Distinct column values

Zyg_D — Sun, 21 Jun 2020 19:15:12 GMT

markdy wrote:
Hey There bugs84,

I think this might help:

ListProductTypes = CALCULATE(CONCATENATEX(VALUES('Table'[ProductType]),'Table'[ProductType],","))

Credit Goes to Data and Analytics with Dustin Ryan:
https://sqldusty.com/2016/06/29/5-more-power-bi-tips/

According to Dusting Ryan:
Cited References:
Dustin, Ryan. 5 More PowerBI Tips. Data and Analytics with Dustin Ryan, 2016. Web. 17 January 2018.

This does not work in the OP's case

Re: Concatenatex with Distinct column values

EduSurveys — Fri, 28 Aug 2020 16:59:36 GMT

markdy , this solution works for me!

Re: Concatenatex with Distinct column values

AriSultani — Tue, 18 Jun 2024 09:23:11 GMT

Unfortunately, it does not work for me; I use the following code, and works perfectly.

CONCATENATEX(DISTINCT(Table[The Column]),Table[The Column],", ")