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:
EVALUATE
{
CALCULATE (
[MyMeasure],
‘MyCalcGroup'[Name] = “item1” || ‘MyCalcGroup'[Name] =
“item2”
)
}
— multipleOrEmptySelectionExpression on MyCalcGroup:
IF(ISFILTERED ( ‘MyCalcGroup’ ),\”Filters: \”&
CONCATENATEX (FILTERS ( ‘MyCalcGroup'[MyCalcGroup]
),’MyCalcGroup'[MyCalcGroup],\”, \”))
IF (
ISFILTERED ( ‘MyCalcGroup’ ),
“Filters: “
& CONCATENATEX (
FILTERS ( ‘MyCalcGroup'[Name] ),
‘MyCalcGroup'[Name],
“, “
)
)
— Returns “Filters: item1, item2”
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.
Be the first to comment