Create a string from columns and use a slicer to search IN the string

[Rjesak]

I'm trying to figure out the best way to organize this.

I have a table describing items - the items can come in a few different colors. 

seq	item	color
111	bike	blue
111	bike	green
112	boat	blue
113	train	blue
113	train	white
113	train	green
114	tricycle	purple

I have two requirements:

1. I want to be able to display all the colors for that item in a single column in a visual separated by a comma:

111	bike	blue, green
112	boat	blue
113	train	blue, green, white
114	tricycle	purple

2. I want to build a slicer that shows the colors and will select any of the rows that include that color. So the slicer options would be

blue

green

white

purple

If the user selected blue, the visual would show:

111	bike	blue, green
112	boat	blue
113	train	blue, green, white

if they selected white, they would see:

111	bike	blue, green
112	boat	blue
113	train	blue, green, white

[Rjesak]

You are both SO awesome and both the solutions work so I just marked the first one as the solution. But thank you both so much!

[FrankAT · Accepted solution]

Hi @Rjesak ,

create a new calculated column like this:

Use a table visual and a slicer as shown in figure:

Sample PBIX-file is attached.

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[CNENFRNL]

Colours = CONCATENATEX( info, Info[color], ", " )

Colors = CALCULATE( [Colours], VALUES( Info[item] ), ALL( Info[color] ) )