Test if it's in the possible values

[#1]

Hello,

I have a graph where I have a dimension ("Categ").

This dimension has 2 values : "AAA" and "BBB". I have a list where I can select the "Categ".

When I select "AAA", I would like to display only the lines for "AAA".

This should be the normal behavior, but in my graph, I have column that use a "{1}" context.

Without selection :

With selection :

So, I want to change my dimension "Categ" to something like "if the current Categ is in the possible Categ regarding the current selection, then Categ".

I used a workaround with this formula :

=if(substringCount(aggr(concat(DISTINCT Categ),Categ), Categ) > 0, Categ)

but it's not "clean".

The result with the formula is OK :

Is there another proper way ?

[johnw · Accepted solution]

Simpler dimension approach:

Dimension = aggr(Categ,Categ)
Expressions = same as you have

But my impression based on some very minor testing is that calculated dimensions are rather slow. So I suspect that a more efficient approach would be this:

Dimension = Categ
Expressions = sum(Value)
if(len(Categ),sum({1} Total <Categ> Value))
if(len(Categ),sum({1} Total Value))

[Oleg_Troyansky]

If you are on version 9, I'd recommend continue using Set Analysis and add a condition that Categ is one of the possible values. Instead of just {1}, your set analysis condition would look like:

{1 < Categ=P(Categ) > }

See attached example

[#4]

All the solutions you suggested work fine.

Thank you very much !