This article explains the differences in writing set expressions inside and outside of the aggregation functions to which they apply. For more information on set expressions and set analysis, see this article.
What are inner and outer set expressions
Inner set expressions are set expressions written inside an aggregation function. For example, below, the set expression is written inside the brackets of the ‘Sum’ function.
Sum({} field_to_sum)
Outer set expressions are written outside an aggregation function. For example, in the function below, the stuff inside the curly brackets is written before the ‘Sum’ function.
{} Sum(field_to_sum)
Difference between inner and outer set expressions
At first glance, there is no difference between the result of inner and outer set expressions. However, imagine an example with more than one aggregation function that you want to modify identically. Assume we are running a small blog and want the average daily revenue for blog visitors from the United States. An inside set expression in this example would have to be written multiple times within each aggregation function:
Sum({} [Revenue])/Count(distinct {} Date)
With the outside set expression, you still only have to write it once – before the first aggregation function:
{} Sum([Revenue])/Count(distinct Date)
You can see that an outside set expression is cleaner in these scenarios.
Set expression scope
The scope of a set expression refers to what it has an impact on within the function. Let’s explore this further below.
Defining outer set expression impact
By default, an outer set expression affects all aggregation functions after it. If you want to affect only specific aggregation functions, enclose the outer set expression and the aggregation function in brackets.
({} Sum([Revenue]))/Count(distinct Date)
The outer set expression above will now only impact the sum function, NOT the count function.
Set expression precedence
The precedence of a set expression refers to the priority in which it is calculated relative to other set expressions in the whole function.
Inner vs outer set expression precedence
Inner set expressions will take precedence over outer set expressions. This means that if the aggregation function has both, an inner and outer set expressions that clash, the inner expression will be prioritised. In the following example, the revenue will only be summed up for the United States. Note there is a way of using signage to add values to the inner set instead of overriding it. See the section ‘Using outer set expressions to modify inner set selections in the same fields’ below.
{} Sum({} [Revenue])
If the set expressions do not clash, they will be merged, and the result will be identical to that of both expressions being written as a single modifying set. For example, when summing up revenue for 2024, we could write the country’s set expression as an inner expression and the year as an outer expression.
{<[Date.Calendar.Year] = {2024}>} Sum({} [Revenue])
Note that if the inner set expression contains a set identifier, it will override the outer set expression in full and be ignored.
Using outer set expressions to modify inner set selections in the same fields
As mentioned above, by default, the inner set expression has precedence over the outer set expression when the set expressions specify clashing selections in the same field. However, using assignment characters can change this behaviour.
The equals assignment character: =
The equals assignment character gives the inner set expression precedence over the outer, merging the expressions where possible. In the following example, the inner set sums the revenue earned within the United States. The outer set would sum the revenue earned within the United Kingdom. Because a simple equals assignment character is used in the inner set, the inner set expression takes precedence, and the revenue is summed for the United States only.
{} Sum({} [Revenue])
The following example highlights what happens if you use the equals assignment character, but your inner and outer set expressions contain different fields. Here, the inner set expression sums the revenue from the United States, while the outer set expression sums the revenue earned in 2024.
{<[Date.Calendar.Year] = {2024}>} Sum({} [Revenue])
The result of this example is the sum of revenue earned in the United States in 2024. The two set expressions are merged.
The plus assignment character
The plus assignment character forces the inner and outer expressions to merge via a union. The results of using assignment characters can be a little confusing, so let’s look at them using examples.
The plus assignment character when inner and outer set expressions contain the same field
Consider these three examples of us summing the revenue from the United States in the inner set expression and revenue from the United Kingdom in the outer set expression while using the plus assignment characters.
Example 1: the plus is assigned to the outer set expression.
{} Sum({} [Revenue])
The result is the sum of revenue from the United States only as though the outer set expression doesn’t exist. Applying any selections in the country field does not change the result.
Example 2: the plus is assigned to the inner set expression.
{} Sum({} [Revenue])
The result is the sum of revenue earned from the United States and the United Kingdom. The results of both set expressions are unioned together. Applying any selections in the country field does not change the result.
Example 3: the plus is assigned to the inner and outer set expression.
{} Sum({} [Revenue])
Without any selections applied, the result is the total revenue earned for all countries, not just the United Kingdom and the United States. If a selection is made in any field other than country, the result will still be as though there is no set expression. If a selection is made for either of the two countries, the result will be as per example 2. Whereas if a selection is made for any country other than the two, the results will be as per example 2 plus the revenue from the country in the selection.
The plus assignment character when inner and outer set expressions contain different fields
Consider these three examples of us summing the revenue from the United States in the inner set expression and revenue earned in 2024 in the outer set expression while using the plus assignment characters.
Example 1: the plus is assigned to the outer set expression.
{<[Date.Calendar.Year] += {2024}>} Sum({} [Revenue])
The result is the sum of revenue from the United States only. Applying any selections in the country field does not change the result. Applying selections in the year field for 2024 results in revenue earned in 2024 in the United States. Selecting any other year than 2024 does not change the result.
Example 2: the plus is assigned to the inner set expression.
{<[Date.Calendar.Year] = {2024}>} Sum({} [Revenue])
The result is the sum of revenue in 2024 for all countries. Applying any selections in the year field does not change the result. Applying selections in the country field for the United States gives the revenue earned in 2024 in the United States. Selecting any other country in the country field gives the revenue earned in 2024 in the United States and the country in the selection.
Example 3: the plus is assigned to the inner and outer set expression.
{<[Date.Calendar.Year] += {2024}>} Sum({} [Revenue])
Without any selections applied, the result is the total revenue earned for all countries and all years. Applying a selection in any other field than country and year gives the result as though there are no set expressions. If a selection is made in the year field for 2024, the result will be the total revenue earned in 2024. Selecting any other value than 2024, the result will be as though no selections have been made in the year field. If a selection is made in the country field for the United States, the revenue will be the total earned in the United States. Applying selections for any other country in the country field gives the revenue earned in the United States and the country in the selection.
The star assignment character
The star assignment character forces the inner and outer expressions to merge via an intersection. The results will vary depending on whether you use the same fields or different fields and where the assignment character is used. Let’s take a look by using examples.
The star assignment character when inner and outer set expressions contain the same field
Consider these three examples of us summing the revenue from the United States in the inner set expression and revenue from the United Kingdom in the outer set expression while using the plus assignment characters.
Example 1: the star is assigned to the outer set expression.
{} Sum({} [Revenue])
The result is the sum of revenue from the United States only as though the outer set expression doesn’t exist. Applying any selections in the country field does not change the result.
Example 2: the star is assigned to the inner set expression.
{} Sum({} [Revenue])
The result is £0. Applying any selections in the country or other fields does not change the result.
Example 3: the star is assigned to the inner and outer set expression.
{} Sum({} [Revenue])
Without any selections applied, the result is £0. If a selection is made in any field other than country, the result will still be £0. If a selection is made for any country other than the United Kingdom (country in the outer expression), the result will be the revenue in the United States only. Any selections made in other fields now will impact the value.
The star assignment character when inner and outer set expressions contain different fields
Consider these three examples of us summing the revenue from the United States in the inner set expression and revenue earned in 2024 in the outer set expression while using the star assignment characters.
Example 1: the star is assigned to the outer set expression.
{<[Date.Calendar.Year] *= {2024}>} Sum({} [Revenue])
The result is the sum of revenue from the United States only in 2024. Applying any selections in the year field for values other than 2024 results in the total sum of revenue for the United States. Applying a selection for a different country does not change the result.
Example 2: the star is assigned to the inner set expression.
{<[Date.Calendar.Year] = {2024}>} Sum({} [Revenue])
The result is the sum of revenue in 2024 for the United States. Applying any selections in the year field does not change the result. Applying a filter for any country other than the United States results in a £0 result.
Example 3: the star is assigned to the inner and outer set expression.
{<[Date.Calendar.Year] *= {2024}>} Sum({} [Revenue])
Without any selections applied, the result is the total revenue earned for the United States in 2024. Applying a selection for any year other than 2024 gives the total revenue earned in the United States. Applying a selection for any country other than the United States results in £0.
Using outer set expressions to modify existing master measures
A particularly useful feature of outer set expressions is that they can be used to modify existing master measures without needing to create additional master measures. For example, assume we have a master measure called “Clicks”, which is the count of all clicks made to ads on a small website. If we wanted to use the master measure to show a KPI for the ad clicks made in 2024 only, we could modify the existing master measure by using an outer set expression to filter for the year as follows:
{<[Date.Calendar.Year] = {2024}>} "Clicks"
The stuff inside the curly brackets is our outer set expression. The “Ad Clicks” is the name of our master measure. If you’re unsure of how to reference master measures in set expressions, check out this blog post.
