6 Replies Latest reply: Oct 14, 2016 9:51 AM by Sunny Talwar

# How to write a massive IF statement like this to produce a colour based on two measures?

Hi all,

Long explanation below but the idea is simple!

I have the following table which defines what a colour should be when comparing output from two Measures:

Measure 1: % Variance

Measure 2: % Projects

For each Measure I already have an individual expression that tells me if the value is in Red, Green or Yellow. These are held in two variables:

Variable1: gives me one specific colour for %Variance

Variable2: gives me one specific colour for % Project

I need a large IF statement (I think unless there is a better alternative) that produces each value in the Output column below based on the colour combination of % Variance and % Projects output.

 % Variance % Projects Output Red Red Red Red Yellow Red Red Green Red Yellow Yellow Yellow Yellow Red Red Yellow Green Yellow Green Green Green Green Red Red Green Yellow Yellow

The basic idea if you were to read it is:

If % Variance is Red and % Projects is Red, then colour is Red,

If % Variance is Red and % Projects is Yellow, then colour is Red

If % Variance is Red and % Projects is Green, then colour is Red

If % Variance is Yellowand % Projects is Yellow, then colour is Yellow

... and so on following the rules of table above.

I tried:

If(\$(v%Variance)='Red' and \$(v%Projects)='Red', 'Red',

If(\$(v%Variance)='Red' and \$(v%Projects)='Yellow', 'Red',

If(\$(v%Variance)='Red' and \$(v%Projects)='Green', 'Red',

If(\$(v%Variance)='Yellow' and \$(v%Projects)='Yellow', 'Yellow',

If(\$(v%Variance)='Yellow' and \$(v%Projects)='Red', 'Red',

If(\$(v%Variance)='Green' and \$(v%Projects)='Green', 'Green',

If(\$(v%Variance)='Green' and \$(v%Projects)='Red','Red',

If(\$(v%Variance)='Green' and \$(v%Projects)='Yellow','Yellow'

)

)

)

)

)

)

But this doesn't work!

Can anyone please provide an IF statement for the above table to produce the output in the Output column?

• ###### Re: How to write a massive IF statement like this to produce a colour based on two measures?

May be use Pick Match instead of if

Pick(Match(\$(v%Variance)&'|'&\$(v%Projects), 'Red|Red', ......),

'Red',

.

.

.

)

• ###### Re: How to write a massive IF statement like this to produce a colour based on two measures?

Or even better option would be to assign a dual value to your variable

Red = 3

Yellow = 2

Green = 1

and then Pick(RangeMax(var1, var2), 'Green', 'Yellow', 'Red')

• ###### Re: How to write a massive IF statement like this to produce a colour based on two measures?

Hi Sunny,

Would you be able to provide a practical working example I can copy?

I tend to get lost when I see so many brackets and commas! Copying a sample and changing it may help with better understanding

• ###### Re: How to write a massive IF statement like this to produce a colour based on two measures?

Try this:

If(WildMatch('|'&\$(v%Variance)&'|'&\$(v%Projects)&'|', '*Red*'), 'Red',

If(WildMatch('|'&\$(v%Variance)&'|'&\$(v%Projects)&'|', '*Yellow*'), 'Yellow', 'Green'))

• ###### Re: How to write a massive IF statement like this to produce a colour based on two measures?

Hi Sunny,

Is this the complete code for the table above or do I need to add? Sorry I am unable to work it out yet

• ###### Re: How to write a massive IF statement like this to produce a colour based on two measures?

I should work, isn't it working?