Deep dive into selection expressions for calculation groups

If you’ve worked with calculation groups, you know how powerful they are for simplifying measure logic and enhancing report functionality. It doesn’t stop there though; selection expressions offer even more fine-tuned control.

Selection Expressions allow fine-tuned control over how calculations behave when certain conditions are met. They introduce additional logic for handling cases where multiple calculation items are selected or when no specific selection is made on a calculation group. This provides a way to do better error handling but also opens interesting scenarios that provide some good default behavior, for example, automatic currency conversion. Selection expressions are optionally defined on a calculation group and consist of an expression as well as an optional dynamic format expression.

Types of Selection Expressions

There are two types of selection expressions consisting of the expression itself and a FormatStringDefinition:

1. Multiple or Empty Selection expression (multipleOrEmptySelectionExpression).
   This expression will be evaluated if the user selects multiple calculation items on the same calculation group or if a conflict between the user’s selections and the filter context occurs.
2. No Selection expression (noSelectionExpression). This expression will be evaluated if the user did not select any items on the calculation group.

These selection expressions are evaluated depending on the type of selection the user makes on the calculation group on which the expressions are defined. The following table shows the effects, assuming your model’s selectionExpressionBehavior setting is set to automatic or nonvisual (see below).

SelectionExpressionBehavior setting

On top of the selection expressions that you can define on a calculation group, we are soon introducing a model level property called selectionExpressionBehavior, which allows you to influence what happens when there is no multipleOrEmptySelectionExpression defined on your calculation groups and when you are grouping by calculation groups in your visual. You can define this for example using TMDL:

createOrReplace
  model Model
    culture: en-US
    defaultPowerBIDataSourceVersion: powerBI_V3
    discourageImplicitMeasures
    sourceQueryCulture: en-US
    selectionExpressionBehavior: nonvisual

The setting can take three values:

1. Automatic: this is the default value and is the same as nonvisual. This ensures that your existing models do not change behavior. At some point in the future, new models set to Automatic will use visual. There will be an announcement at that time.
2. Nonvisual – if the calculation group does not define a multipleOrEmptySelection expression the calculation group returns SELECTEDMEASURE() and when grouping by the calculation group, subtotal values are hidden.
3. Visual – if the calculation group does not define a multipleOrEmptySelection expression the calculation group returns BLANK(). When grouping by the calculation group, subtotal values are determined by evaluating the selected measure in the context of the calculation group.

By leveraging selection expressions, you can ensure that reports react dynamically, providing relevant calculations even in cases where selections might be unclear or missing.

Using Selection Expressions

Let’s look at some examples of selection expressions in action.

Multiple or empty selections

If the user makes multiple selections on the same calculation group, the calculation group would return the same result as if the user did not make any selections. Now, you can specify a multiOrEmptySelectionExpression on the calculation group. If you do, then that expression is evaluated and its result is returned, formatted using the related dynamic format string (if specified). For example, you can use this to inform the user about what is being filtered. For example, if we define the following multipleOrEmptySelection expression on the MyMeasure calculation group:

IF (
ISFILTERED ( 'MyCalcGroup' ),
    "Filters: " 
         & CONCATENATEX ( 
  	            FILTERS ( 'MyCalcGroup'[Name] ),
            'MyCalcGroup'[Name], 
            ", "
     	   )
)

Then, making multiple selections on the calculation group will evaluate the multipleOrEmptySelection expression:

EVALUATE
{
    CALCULATE (
        [MyMeasure],
        'MyCalcGroup'[Name] = "item1" || 'MyCalcGroup'[Name] = "item2"
    )
}

-- Returns “Filters: item1, item2”

Previously, in case of a conflicting or empty selection on a calculation group you might have seen this error before:

This error is a thing of the past and we will evaluate the multipleOrEmptySelectionExpression on the calculation group (if present). If that expression is not defined, we will not filter the calculation group, i.e. return SELECTEDMEASURE() unless you change the selectionExpressionBehavior setting on your model to visual.

No selections

One of the best showcases for this scenario is automatic currency conversion. If you use calculation groups to do currency conversion, the report author and user must remember to select the right calculation group item for the currency conversion to happen. With selection expressions, you are empowered to do automatic currency conversion using a default currency. On top of that, if the user wants to convert to another currency altogether, they can still do that, but even if they deselect all currencies the default currency conversion will still be applied.

Calculation group does not define a noSelectionExpression

Note how both the currency to convert to as well as the “conversion” calculation group item is selected.

Calculation group does define a noSelectionExpression

Notice how the user only has to select the currency to convert to as the default currency is set using the noSelectionExpression.

Using the selectionExpressionBehavior setting

As mentioned above, we are also soon introducing a new model level setting called selectionExpressionBehavior. This setting influences the behavior of all calculation groups in your model. In these examples we use the following calculation group which defines two calculation items:

table Scenarios
        calculationGroup
            calculationItem Current = SELECTEDMEASURE()
            calculationItem Times2 = SELECTEDMEASURE()*2
        column 'Scenario Options'
            dataType: string
            summarizeBy: none
            sourceColumn: Name
            sortByColumn: Ordinal
            annotation SummarizationSetBy = Automatic
        column Ordinal
            dataType: int64
            formatString: 0
            summarizeBy: sum
            sourceColumn: Ordinal
            annotation SummarizationSetBy = Automatic

Automatic or nonvisual setting

In this mode, subtotals are hidden when grouping by calculation groups and if a user makes a multiple or empty selection on a calculation group that does not define a multipleOrEmptySelection expression, then the calculation group returns SELECTEDMEASURE(). Of course, if a multipleOrEmptySelection expression is defined, that is evaluated, and its result is returned.

At first, our calculation group does not define a multipleOrEmptySelection expression. Therefore, the subtotals are hidden and SELECTEDMEASURE() is returned:

Now, we add the following selectionExpression to our calculation group:

multipleOrEmptySelectionExpression =
IF (
ISFILTERED('Scenarios'),
"Filters: " 
& CONCATENATEX ( 
FILTERS ( 'Scenarios'[Scenario Options] ),
'Scenarios'[Scenario Options], 
", "
)
)

Then, as we select multiple values for our calculation group, the multipleOrEmptySelectionExpression gets evaluated whenever there are multiple calculation items in the selection and the subtotals are empty:

Visual setting

In this mode, when grouping by calculation groups, subtotals are determined by evaluating the selected measure in the context. If the calculation group does not define a multipleOrEmptySelection expression, the calculation group returns BLANK().

At first, our calculation group does not define a multipleOrEmptySelection expression:

And then once it is defined once more, the multipleOrEmptySelection expression is evaluated, but this time even on the subtotals:

In summary

As you can see, selection expressions and the selectionExpressionBehavior setting offer a lot of flexibility to decide exactly what happens as your users are interacting with the calculation groups in your reports.

We are looking forward to seeing what you will build using these advanced features. Let us know what you think!

To learn more, refer to the Selection Expressions documentation.