I am trying to create a webi report based off two queries from a universe
Query 1 produces the following data
| ID | Product |
|---|---|
| 1 | Bicycle 1 |
| 2 | Bicycle 2 |
| 3 | Bicycle 3 |
| 4 | Bicycle 4 |
Query 2 produces the following data
| ID | FID | Color |
|---|---|---|
| 1 | 1 | Red |
| 2 | 1 | Blue |
| 3 | 1 | Green |
| 4 | 2 | Blue |
| 5 | 3 | Green |
| 6 | 3 | Red |
My report is based off query 1. However I would also like to bring in the related values in a column called colors like below.
| ID | Product | Colors |
|---|---|---|
| 1 | Bicycle 1 | Red, Blue, Green |
| 2 | Bicycle 2 | Blue |
| 3 | Bicycle 3 | Green, Red |
| 4 | Bicycle 4 |
I have created a merged dimension between product.id and color.fid
I have created a variable called biycleColors which has the following formula in it: =[Colors] but its only bringing in data if theer is one related value or its saying #MULTIVALUE. Is there a way to bring in multiple strings into one cell and if so can someone help me with the formula?
TIA