cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
loganwol Frequent Visitor
Frequent Visitor

Concatenatex with Distinct column values

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

3 REPLIES 3
bugs84 Frequent Visitor
Frequent Visitor

Re: Concatenatex with Distinct column values

Hi all,

I'm looking for the same...

 

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

 

how can I get only distinct ProductType values?

Highlighted
markdy Frequent Visitor
Frequent Visitor

Re: Concatenatex with Distinct column values

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:

Dustin_Ryan_20170117.PNG

Cited References:

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

zapppsr Member
Member

Re: Concatenatex with Distinct column values

Very good, thanks!