Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Greetings,
I really did not know what to call this, so I will give you my scenario and hope that someone has accomplished this already
I have a dimension that has 500 values. In my example it is model of cars. Each car can have multiple colors. Dimension 2 is the color. My goal is to create a straight table where I can aggregate the possible colors for each model into one cell. It is possible for there to be 10 red mustangs, so I would only want red listed once in the Aggregation expression. Optimally the values in the expression are separate by commas, but any other delimiter is fine. There is a unique field that has a ID number for each car, but I will not use that in the chart, so there are not really duplicate records in the table of data.
Unfortunately, I can not share my actual data, but if you can solve this problem, I can apply it to my data model.
Try creating a straight table chart with dimension [Car Model] and expression
=Concat(DISTINCT [Color], ', ')
Try creating a straight table chart with dimension [Car Model] and expression
=Concat(DISTINCT [Color], ', ')
Thanks for your quick response on a Sunday!
That works great except that it puts a comma after the last value
Red, Blue, Green,
The simple solution is to change the delimiter to a space, but is there a way to tell it to only put the delimiter between the values and not after the last possible one?
Red, Green, Blue
It shouldn't work like this, there should not be an extra delimiter after the last value.
Could you post your exact expression you are using?
Stefan,
You are correct, I had an '&' between the dimension and the delimiter, so it was adding the delimiter each time. It was left over from my previous efforts and I had not removed it. When I use your exact syntax, I do get the desired results. I went ahead and added a space after the comma in the delimiter ', ' so that I could read the results easier. I do appreciate your assistance!