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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!