Power BI Blog: Selection Expressions for Calculation Groups

Welcome back to this week’s edition of the Power BI blog series.  This week, we look at how selection expressions – and their behaviour in particular – is changing for calculation groups.

Calculation Groups in Power BI allow you to apply a single calculation across multiple measures without the need to create additional calculated columns or measures. This not only saves time but also enhances the manageability of your data model.

These have now become more powerful.  Microsoft has recently introduced into Preview revised selection expressions for calculation groups, which allow you to influence what happens in case the user makes multiple selections for a single calculation group or does not make a selection at all.  This provides not only a better way to perform error handling, it also opens interesting scenarios that provide some good default behaviours, e.g. automatic currency conversion.  Selection expressions are optionally defined on a calculation group and consist of an expression and an optional dynamic format expression.

This new capability comes with an extra benefit of potential performance improvements when evaluating complex calculation group items.

To define and manage selection expressions for calculation groups, you can leverage the same tools you use today to work with calculation groups.  On a calculation group you will be able to specify the following selection expressions both consisting of the Expression itself and a FormatStringDefinition:

• multipleOrEmptySelectionExpression: this expression has a default value of SELECTEDMEASURE() and will be returned 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
• noSelectionExpression: this expression has a default value of SELECTEDMEASURE() and will be returned if the user did not select any items on the calculation group.

Here’s an overview of the type of selection compared to the current behaviour that was shipped before this Preview, and the new behaviour both when the expression is defined on the calculation group and when it’s not.  The items in bold are where the new action differs from the previous approach.

To illustrate, here are some examples.

Multiple or Empty Selections

If the user makes multiple selections on the same calculation group, the current behaviour is to return the same result as if the user did not make any selections.  In this Preview, you can specify a multipleOrEmptySelectionExpression on the calculation group.  If you did, then we evaluate that expression and related dynamic format string and return its result.  You can for example use this to inform the user about what is being filtered:

In case of a conflict or empty selection on a calculation group, you might have seen this error before:

With this new behaviour, this error is consigned to history and will evaluate the multipleOrEmptySelectionExpression if present on the calculation group.  If that expression is not defined, Power BI will not filter the calculation group.

No Selections

One of the best showcases for this scenario is automatic currency conversion.  Today, 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 this Preview, you are now 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.

CURRENT

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

NEW

Notice how the user must only select the currency to convert to.

In the meantime, please remember we offer training in Power BI which you can find out more about here.  If you wish to catch up on past articles, you can find all of our past Power BI blogs here.