Introduction

In my previous post Creating alias columns for calculation groups I showed how to create alias columns containing alternate names for calculation items in a calculation group by using the Group By Columns property.

To recap: By setting the Group By Columns property of any alias columns to be the original calculation item column, the original calculation item column is automatically filtered when the alias column is filtered, thereby applying the corresponding calculation item.

In my sample model for this post, the calculation item column is 'Time Intelligence'[Time Calc] and I created a partly abbreviated columns 'Time Intelligence'[Time Calc Short]:

It turns out that Selection Expressions for calculation groups (announced as a preview feature in the March 2024 Power BI update) give us another way of implementing alias columns, with some slight differences.

What are Selection Expressions and how can we use them?

Before Selection Expressions were available, calculation groups had no effect on measures unless exactly one calculation item was filtered. In DAX terms, the following expression had to be true for a calculation item to be applied:

HASONEFILTER ( <Calc Item Column> )

The introduction of Selection Expressions relaxes this requirement. Selection Expressions allow a calculation group to have an effect in two scenarios when a single value has not in fact been filtered in the calculation item column. These two scenarios are shown in the table below (<CIC> refers to the calculation item column):

Selection Expressions therefore allow a kind of exception handling. Each of the two Selection Expressions is effectively a special calculation item that is applied if the appropriate condition above is met.

The two Selection Expressions have a default value of SELECTEDMEASURE ( ) if they are not defined.

How can we use Selection Expressions to our advantage?

How can we create Selection Expressions?

As of March 2024, the Power BI Desktop interface does not appear to support authoring Selection Expressions. Instead, the options I am aware of to author Selection Expressions are:

1. Update the model’s Compatibility Level to 1605 by either:
   1. Using Tabular Editor, temporarily switching on “Allow unsupported modeling operations” then entering “1605” under Model > Database > Compatibility level.
   2. Saving the model as a PBIP, closing Power BI Desktop, editing the appropriate file then reopening (thank you Daniel Otykier for this suggestion):
      • model.bim: "compatibilityLevel": 1605
      • database.tmdl: compatibilityLevel: 1605
2. In Tabular Editor, select the calculation group, and in the Expression Editor select the appropriate Selection Expression (or format string) from the drop-down menu.
3. Enter the DAX expression in the same was as you would or any calculation item. A screenshot from Tabular Editor 3 is shown below:

What is the DAX for my No Selection Expression?

In my sample model, I created this No Selection Expression for the ‘Time Intelligence’ calculation group:

-- No Selection Expression in 'Time Intelligence' calculation group
IF (
    ISFILTERED ( 'Time Intelligence' ),
    CALCULATE (
        SELECTEDMEASURE ( ),
        VALUES ( 'Time Intelligence'[Time Calc] )
    ),
    SELECTEDMEASURE ( )
)

Each Selection Expression has a corresponding format string expression, just like regular calculation items. I defined the No Selection Format String Expression in a similar way:

-- No Selection Format String Expression in 'Time Intelligence' calculation group
IF (
    ISFILTERED ( 'Time Intelligence' ),
    CALCULATE (
        SELECTEDMEASUREFORMATSTRING ( ),
        VALUES ( 'Time Intelligence'[Time Calc] )
    ),
    SELECTEDMEASUREFORMATSTRING ( )
)

The logic of the above No Selection Expression is:

1. Check whether a filter has been applied to any column of the 'Time Intelligence' table.
   The reason for including this check is to handle calculation groups with single calculation items. In order to mimic the traditional behaviour of calculation groups, I didn’t want any calculation item to be applied unless a filter had been placed on the 'Time Intelligence' table.
2. If a filter has been applied, then take the visible value(s) of the calculation item column 'Time Intelligence'[Time Calc], apply them as a filter, and calculate SELECTEDMEASURE ( ).
   1. If a single value of 'Time Intelligence'[Time Calc] is visible, then that calculation item will be applied.
   2. If multiple or no values of 'Time Intelligence'[Time Calc] are visible, then no calculation item is applied (which mimics the traditional calculation group behaviour).
      Note: If I had specified a Multiple or Empty Selection Expression, it would have been applied instead, however I didn’t need to do that here.

Does this work in the report?

To test this, I created a new calculated column 'Time Intelligence'[Time Calc Short (no group by)] as a duplicate of 'Time Intelligence'[Time Calc Short]. However, for 'Time Intelligence'[Time Calc Short (no group by)] I have not set the Group By Columns property.

----------------------------------------------------------
-- Calculated Column: 'Time Intelligence'[Time Calc Short]
----------------------------------------------------------
COLUMN 'Time Intelligence'[Time Calc Short] = 
    VAR TimeCalc = 'Time Intelligence'[Time Calc]
    RETURN
        SWITCH (
            TimeCalc,
            "Current", "Cur",
            "Last Year", "LY",
            "Year on Year", "YOY",
            "Year on Year %", "YOY %",
            TimeCalc
        )
    DisplayFolder = "Calculation Items"

------------------------------------------------------------------------
-- Calculated Column: 'Time Intelligence'[Time Calc Short (no group by)]
------------------------------------------------------------------------
COLUMN 'Time Intelligence'[Time Calc Short (no group by)] =
    'Time Intelligence'[Time Calc Short]
    DisplayFolder = "Calculation Items"

In the sample report, the Matrix on the second page uses 'Time Intelligence'[Time Calc Short (no group by)] on the columns.

I had to make one small tweak to the Matrix visual: disable Column Subtotals. This is because Power BI automatically suppresses totals when calculation item columns are used as grouping fields (as was the case using the Group By Columns method or the calculation item column itself), but not when other columns are used.

After switching off Column Subtotals, the Matrix on the second page indeed appears identical to the Matrix on the first page which uses 'Time Intelligence'[Time Calc Short]. 😊

Key differences between the two methods

There are some important differences to note between this method using the No Selection Expression and the Group By Columns method:

I am sure that there are many more interesting and creative applications of Selection Expressions, including variations or improvements on the method I have presented here.

I’m looking forward to hearing your thoughts. Please comment below!

All the best,
Owen

• Twitter
• LinkedIn