Color a Dimension if it matches two parameters (if match AND)

[chriys1337]

I am trying to color a dimension green if the value in this dimension is  accuring two times in another dimension.

Dim1: Validity, Dim2: Named Account

Validity, Named Account

ST, Loreal

MT, Loreal

MT, Cisco,

LT, Stabilo

I would like to color the Dim Named Account green if one account has the Validity ST AND MT.

I tried it with entering the Dimension and fill following formula in the "Text Color" Area of the Dimension "Named Account".

if(match(Validity,'ST') and match(Validity,'MT'),green())

Unfortunately this is not working, if just one first parameter if(match(Validity,'ST'), green()) it is working.

Does anybody know how to achieve this?

I have added a sample app, the single Account is now green due to the parameter match(Validity,'ST'), the question is how to do with an and-condition?

[tresesco]

If you don't use NODISTINCT aggr() by default work for single value (the first value internally).

[chriys1337]

Thank you Jonathan and Tresesco, both approaches are working! Reall great, I am happy!

[chriys1337]

Hej Jonathan,

could you please explain a little bit how this is working?

Tried to figure it out with the help of Les Set Analasis pdf but did not come far.

=If(Count({<Account = P({<Validity = {'ST'}>} Account) * P({<Validity = {'MT'}>} Account)>} Account) > 0, Green())

What is this here doing:

({<Account = P({<Validity = {'ST'}>} Account) * P({<Validity = {'MT'}>} Account)>} Account)

I was confused by the 4 times "Account"?

[jonathandienst]

P() = the set of possible values. and as said earlier * is the intersect operator

P({<Validity = {'ST'}>} Account) * P({<Validity = {'MT'}>} Account)

This is the intersection of accounts that have an ST and the accounts that have an MT. in other words, the accounts that have at least one ST AND at least one MT.

[chriys1337]

Thank you Jonathan!