Concatenatex with Distinct column values

[loganwol]

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

[bugs84]

Hi all,

I'm looking for the same...

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

how can I get only distinct ProductType values?

[markdy · Accepted solution]

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.

[AriSultani]

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

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

[EduSurveys]

@markdy , this solution works for me!

[Zyg_D]

---

@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

[zapppsr]

Very good, thanks!