Mixing (AND and OR) or (AND and IF THEN ELSE)

Friday 9 September 2016 @ 9:39 am

One common problem, especially in selection formulas, is when you have both AND and OR but no parentheses to clarify which should come first.   Take the following example:

{Orders.Order Amount} > 5000 and
{Customer.Country} <> "USA" or
{Customer.Region} = "CA"

If there is a CA order for $2,500, will it meet the criteria?  Using the above formula, yes.  That is because without any parentheses, the AND takes priority over the OR.  So in English this formula means:

Return all records that are outside the US and above $5,000. Also return all records that are in CA regardless of the amount.

If I intended that ALL locations were above $5,000  then I need to put parentheses around the OR to tell the report to treat that as one rule.  It could look like this:

{Orders.Order Amount} > 5000 and
( {Customer.Country} <> "USA" or
{Customer.Region} = "CA" )

A similar thing happens when you include an IF THEN ELSE in your selection formula like this:

if {?StateParameter} = "ALL"
then True
else {?StateParameter} = {Table.State}
and {Table.Date} in {?DateRange}

In this case the AND will combine the DateRange rule with the ELSE.  The DateRange parameter will be ignored when you select ALL States.  To apply the DateRange in ALL cases you need to put the IF THEN ELSE statement inside a pair of parentheses like this:

( if {?StateParameter} = "ALL"
then True
else {?StateParameter} = {Table.State} )
and {Table.Date} in {?DateRange}

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro