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

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

1 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:

Dustin_Ryan_20170117.PNG

Cited References:

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

View solution in original post

5 REPLIES 5
bugs84
Frequent Visitor

Hi all,

I'm looking for the same...

 

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

 

how can I get only distinct ProductType 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.

View solution in original post

@markdy , this solution works for me!

Zyg_D
Responsive Resident
Responsive Resident


@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:

Dustin_Ryan_20170117.PNG

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

Very good, thanks!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!