Implementing Extended Syntax Operators

You specify input and output contexts explicitly with context operators of the extended syntax. The table lists the context operators.

The ForAll and ForEach operators are useful when you have a default context with many dimensions. It is often easier to add or subtract from the context using ForAll and ForEach than it is to specify the list explicitly using the In operator.

The In operator specifies dimensions explicitly in a context.

The example in the figure, In Context Operator to Specify Dimensions, shows a report with Year and Sales revenue. Your data provider also contains the Quarter object, but you do not include this dimension in the block. Instead, you include an additional column to show the maximum Sales revenue by Quarter in each Year.

Example: Using In to specify the dimensions in a context.

You can see where the values in the Max Quarterly Revenue column come from by examining this block with a block that includes the Quarter dimension.

In Context Operator with Additional Dimension

The Max Quarterly Revenue column shows the highest quarterly revenue in each year. For example, Q4 has the highest revenue in 2018, so the Max Quarterly Revenue shows the Q4 revenue on the row showing 2018.

Using the In operator, the formula for Max Quarterly Revenue is as follows:

=Max([Sales revenue] In ([Year] ; [Quarter])) In ([Year])

This formula calculates the maximum sales revenue for each (Year, Quarter) combination, then outputs this figure by year.

Note

The default output context of the block is Year, so you do not need to specify the output context explicitly in this formula.

The Where Operator

The Where operator restricts the data used to calculate a measure.

For example, the variable Sales revenue 2018 Q3 calculates the Sales revenue for 2018 Q3:

=[Sales revenue] Where ([Year]="2018" And [Quarter]="Q3")

Note

The In operator specifies the dimensions that feed into the calculation, and the Where operator restricts the data to calculate the measure.

Watch this video to learn about the Input Context ForEach.

Watch this video to learn about Context Operator ForAll.