• Naming multiple values (Between, InList)
  • Handling repeated values (All, Distinct)
  • Boolean Operators (And, Or, Not)
  • Contexts (In, ForEach, ForAll)


Between, InList

Between and InList can select multiple values more easily. Both can be used in situations where the formula would include multiple "[Object_name]=value" statements would be strung together with ANDs.

Between Syntax: [Object_name] Between(first_value;last_value)

  • first_value and last_value can be numbers, text in quotation marks or objects

Notes: Between includes the first value, the last value and everything in between. The first and last values must be separated by a semicolon with no spaces.

InList Syntax: [Object_name] InList(first_value;second_value;etc_value)

  • first_value, second_value and etc_value can be numbers, text in quotation marks or objects

Notes: InList includes all of the values individually written out and each separated from the next by a semicolon and no spaces.

Example: [Number]=1 AND [Number]=2 AND [Number]=3 AND [Number]=5 AND [Number]=6

Using Between: [Number] Between (1;3) AND [Number] Between(5;6)

Using InList: [Number] InList(1;2;3;5;6)

Distinct, All

Syntax: e.g. Count([Object_name];Distinct)

Note: Distinct and All usually come after a semicolon within a function.

Explanation: The operators Distinct and All can be helpful in situations where a function must handle multiple rows with the same value.

Example: [Revenue] has the following values: 5, 5, 6 and 4.

Count([Revenue];Distinct) returns 3 because it has counted the 5 only once

Count([Revenue];All) returns 4 because it has counted the 5 twice


Boolean Operators AND, OR and NOT are handled in the Boolean Operators Guide.

In, ForEach, For All

These are covered in the Contexts Guide.