combine separate dimensions in layout

[wanyunyang]

Hi guys,

Here's a simplified example of the question I have:

Here's my script:

sales:
load * inline
[
Item, Qty
A,1
B,2
C,3
];

color:
load * inline
[
Item, Color
A, Red
B, Yellow
C, Blue
];

sales:
load * inline
[
Item, Shape
A, Round
B, Square
C, Triangle
];

I'm summarizing item sales by different dimensions:

1. By color

2. By shape:

But user wants to combine all dimensions in one table. So this is what I tried: 

Field: =IF($Field='Color' or $Field='Shape', $Field)

Value: =Pick(Match($Field, 'Color', 'Shape'), Color, Shape)

Qty: =SUM(Qty)

And here's the result:

But the problem is: user is not able to filter. For example: If I select "Red", then clear it, "Shape" will disappear, only "Color" left.

Any better idea to achieve this? This is just a simple example, my real model is much more complex, so I would rather do it from layout, not script, in order to avoid some circular reference issues.

Any idea helps. Thank you in advance!

[Gopi_E]

give exact what type of filters you want

[Vegar]

Any selection in dimension or field will make a selection in the hidden field $Field. Try using only({1}$Field) in your conditional statements.

[wanyunyang]

I would like to choose values from both attributes, for example, both "Blue" and "Triangle".

[wanyunyang]

Hi Vegar, thank you for the reply! Is it possible I could choose from both attributes?

[Vegar · Accepted solution]

Maybe adjusting the expression to this would help?

=SUM({<$Field>}Qty)

(See attached qvf)