# Use a measure to return a string of values



## peter789 (Nov 26, 2016)

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 (Nov 26, 2016)

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


----------



## peter789 (Nov 26, 2016)

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


----------



## peter789 (Nov 27, 2016)

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 (Nov 27, 2016)

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


----------

