Skip to main content

References to fields, measures, and variables

ON THIS PAGE

References to fields, measures, and variables

An expression can contain references to fields, variables, and measures.

In most cases, expressions are aggregations, that is calculations that potentially can span multiple records. This means that all field references in an expression must be wrapped in an aggregation function. If no aggregation function is used, the Only() function is used.

When the name of a measure is used inside an expression, it is interpreted as an alias for the measure. This allows you to re-use an already defined entity. You can only re-use master measures and measures inside the same chart in this way.

You can define a measure recursively. In other words, the measure can contain a reference to itself. However, you should only do this in charts with few rows. Performance degrades if the chart has many rows. If there are hundreds of rows, the recursive definition will stop working altogether.

A variable can be used in two different ways in a direct reference or in a dollar expansion. If you use a direct reference, the variable value will be used in the calculation. If you use a dollar expansion, the entire dollar expansion will be replaced by the variable value before the expression is parsed. Therefore, the two different methods may return different results.

How names are interpreted

A name inside an expression can be a reference to a field, a variable, a function, or a measure. Depending on situation, the name is interpreted differently.

Example:  

The string XXX represents a field, a variable, a function, or a measure. XXX will be interpreted as one of these depending on how you create the expression.

Examples of how names are interpreted
Expression XXX interpreted as
XXX measure, variable, or field
$(XXX) variable
Count(XXX) field or variable
XXX() function

You should not use the same name for a field and a variable (or a measure). But if you do and there is ambiguity, the following order of precedence is used:

  • If the name is found inside an aggregation function, a field has precedence over a variable.

  • If the name is found outside an aggregation function, a measure has precedence over a variable, which in turn has precedence over a field.

Rules for expressions

The following rules apply for chart expressions:

  • If a field reference isn’t wrapped in an aggregation function, the Only() function is used by the engine.

  • All expressions return a number and/or a string, whichever is appropriate.

  • Logical functions and operators return 0 for False, -1 for True. Number-to-string conversions and string-to-number conversions are implicit.

  • Logical operators and functions interpret 0 as False and all else as True.

  • Expressions that cannot be correctly evaluated, for example as a result of incorrect parameters or functions, return NULL.