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!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.