# Use a measure to return a string of values

#### peter789

##### Board Regular
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

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### Matt Allington

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

#### peter789

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

#### peter789

##### Board Regular
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

##### MrExcel MVP
Replace DISTINCT with VALUES. DISTINCT keeps blanks, values removes them.

Replies
0
Views
183
Replies
0
Views
225
Replies
0
Views
88
Replies
6
Views
158
Replies
1
Views
139

1,191,353
Messages
5,986,166
Members
440,008
Latest member
Cmbuck

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back