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.
