Use a measure to return a string of values

[peter789]

Firstly please excuse me if I am not using the right technical words to describe my problem as I am new to PowerPivot.
What I have been asked to achieve can be described like this:
I have a number of production lines that on each day of operation can make a different coloured product. For example Red, Green or Blue; I have created a related column with abbreviations; R, G, B. When I return the data to a powerpivot table grouped by Production Year I need to create a field containing a list of the colours produced on that line in the year so it could be just "R" or "R B" or "R G B" etc. I think Distinct will create an invisible table with the unique values in it and I know I can perform operations such as count to tell me how many different values there are. Can I get the actual values out and concatenate them in a string? I have seen some reference to Cubeset but am at a bit of a loss to understand how to use the function.
Many Thanks
Peter

 

[Matt Allington]

If you are using Excel 2016, there is a function concatenatex that will do what you want.

 

[peter789]

Matt
Thanks for the help. I can't wait to try it!
...Maybe tomorrow as it's nearly midnight.
Peter

 

[peter789]

CONCATENATEX works fine. I used the following expression: Colours:=
CONCATENATEX(DISTINCT(FurnaceData_All[Col_abb]),FurnaceData_All[Col_abb]," ",FurnaceData_All[Col_abb],ASC)
The only slight niggle is that on some days there is no production so the colour is blank which returns a leading "," if I chose that as the delimiter rather than a space. What is the best way to filter the results?
Peter

 

[Matt Allington]

Replace DISTINCT with VALUES. DISTINCT keeps blanks, values removes them.