Set expressions can be used inside and outside aggregation functions, and are enclosed in curly brackets.
When you use a set expression inside an aggregation function, it can look like this:
Example: Inner set expression
Sum( {$<Year={2021}>} Sales )Use a set expression outside the aggregation function if you have expressions with multiple aggregations and want to avoid writing the same set expression in every aggregation function.
If you use an outer set expression, it must be placed at the beginning of the scope.
Example: Outer set expression
{<Year={2021}>} Sum(Sales) / Count(distinct Customer)If you use a set expression outside the aggregation function, you can also apply it on existing master measures.
Example: Outer set expression applied to master measure
{<Year={2021}>} [Master Measure]A set expression used outside aggregation functions affects the entire expression, unless it is enclosed in brackets then the brackets define the scope. In the lexical scoping example below, the set expression is only applied to the aggregation inside the brackets.
Example: Lexical scoping
( {<Year={2021}>} Sum(Amount) / Count(distinct Customer) ) – Avg(CustomerSales)Rules
Lexical scope
The set expression affects the entire expression, unless it is enclosed in brackets. If so, the brackets define the lexical scope.
Position
The set expression must be placed in the beginning of the lexical scope.
Context
The context is the selection that is relevant for the expression. Traditionally, the context has always been the default state of current selection. But if an object is set to an alternate state, the context is the alternate state of the current selection.
You can also define a context in the form of an outer set expression.
Inheritance
Inner set expressions have precedence over outer set expressions. If the inner set expression contains a set identifier, it overrides the context defined in the outer set expression. Otherwise, the inner and outer set expressions are both evaluated.
Example 1: Inner set expression with set identifier
In the above expression, the inner set expression Count({1} distinct OrderNumber) consists of the set identifier {1}. When this inner set expression is evaluated, the scope defined in the outer set expression {<Year={2023}>} is not applied.
Example 2: Inner set expressions without set identifiers
In the above expression, the inner set expressions Sum ({<Status={'Confirmed'}>} Sales_Stream1) and Sum ({<UpdatedStatus={'Confirmed'}>} Sales_Stream2) do not contain set identifiers. Therefore, the outer set expression {<Year={2023}>} and both inner set expressions are all applied when evaluating the result.
Element set assignment
The element set assignment determines how the two selections are merged. If a normal equals sign is used, the selection in the inner set expression has precedence. Otherwise, the implicit set operator will be used.
-
{<Field={value}>} - this inner selection replaces any outer selection in “Field”.
-
{<Field+={value}>} - this inner selection is merged with the outer selection in “Field”, using the union operator.
-
{<Field*={value}>} - this inner selection is merged with the outer selection in “Field”, using the intersection operator.
Inheritance in multiple steps
The inheritance can occur in multiple steps. Examples:
-
Current Selection → Sum(Amount)
The aggregation function will use the context, which here is the current selection.
-
Current Selection → {<Set1>} Sum(Amount)
Set1 will inherit from current selection, and the result will be the context for the aggregation function.
-
Current Selection → {<Set1>} ({<Set2>} Sum(Amount))
Set2 will inherit from Set1, which in turn inherits from current selection, and the result will be the context for the aggregation function.
The Aggr() function
The Aggr() function creates a nested aggregation that has two independent aggregations. In the example below, a Count() is calculated for each value of Dim, and the resulting array is aggregated using the Sum() function.
Example:
Sum(Aggr(Count(X),Dim))Count() is the inner aggregation and Sum() is the outer aggregation.
-
The inner aggregation does not inherit any context from the outer aggregation.
-
The inner aggregation inherits the context from the Aggr() function, which may contain a set expression.
-
Both the Aggr() function and the outer aggregation function inherit the context from an outer set expression.