Creating one dimension from several columns of data

[Anonymous]

I want to create a new dimension that allows me to measure what was previously multiple dimensions. For example, imagine I had the following dataset:

                                    Blue                          Purple                       Green                             Red

Amanda                         X                               X                                                                     X

Bob                                X                               X                              X                                    X

Amanda                         X                                                                                               

Carla                                                               X                               X                                    X

Danielle                         X                               X                                                                

Edward                          X                                                                X                                  

Bob                                                                 X                               X                                    X

Danielle                         X                               X                               X                                    X

I want to create a new dimension called Colors that will allow me to measure all of these together. For example, if I created a stacked bar chart with Name and Color both selected as Dimensions, and something like count(Color) as the expression, it would show 2 blue, 1 purple, and 1 red for Amanda; 1 blue, 2 purple, 2 green, and 2 red for Bob; etc.

Anyone know how to do this? I started to do an if statement like:

if (Blue = 'X', 'Blue', if(Purple = 'X', 'Purple', if(Green = 'X', 'Green, if(Red = 'X', 'Red', '')))) as Color

And this was good, except it only counted each row once. So instead of the first row of Amanda getting 1 blue, 1 purple, and 1 red for Color, she only got 1 blue because the if statement stopped there. 

[trdandamudi · Accepted solution]

Please see the attached. I am assuming your data is in excel and I created a excel file based on your data. Hope this helps: