Expression in dimension

[hansrokers]

I have created a straight table chart with several dimensions and expressions.

I want to use this valid expression in a dimension but that does not seem to be allowed:

=if(SubStringCount(Concat(DISTINCT field1,'|'),field2)=0,field3)

Is there an easy way to get this done in stead of using this in all expressions?

[jonathandienst · Accepted solution]

You probably need to use Aggr() to get the correct contexts for the Concat. It is not clear from your explanation how field1, field2 and field3 are associated, so I cannot say exactly how it should look, but it will be something along the lines of:

Aggr(If(SubStringCount(Concat(DISTINCT field1,'|'), field2) = 0, field3), field3)

In general, it would be like

Aggr(If(SubStringCount(Concat(DISTINCT field1,'|'), field2) = 0, field3), ....)

where ... is the list of dimensions in the virtual table constructed by Aggr()