Exploring slicer default selections

By Category: Power BITags: , , , , , ,
Please download the PBIX for this post 🙂

Have you ever wanted a slicer or filter in your Power BI report to select the most recent date by default (or something similar) when it is opened?

In my experience this is quite a common request, and in this post I’ll share some solutions I’ve been experimenting with.

Isn’t this already on Microsoft’s roadmap?

There is indeed an exciting roadmap of updates for Power BI slicer visuals planned which I expect to make default selections much easier to handle (see this LinkedIn post and comment). As a result, my blog post may very well age like milk, but for now it’s drinkable! 🥛

Oh, and I highly recommend you follow PBI Core Visuals and Miguel Myers on LinkedIn if you aren’t already!

What problem are we trying to solve?

Let’s suppose I have a Power BI report where the current month and six previous months can be selected on a slicer. If I publish the report today (7 April 2024) with the current month (“Apr-24”) selected then all is well, and anyone opening the report will initially see “Apr-24” selected 🙂

However, when 1 May rolls around and the semantic model is refreshed, while “May-24” is now available for selection, “Apr-24” is still selected ☹️

Why is this? Slicers in Power BI reports are “sticky” by nature. The values selected at time of publishing (or the last values selected by a given user if Persistent filters is enabled) remain selected whenever the report is opened.

In our example, wouldn’t it be nice if May-24 were selected automatically, either saving me from updating the published report every month, or saving report consumers from changing the month selection themselves!

Note: For this post, I am considering default values that can be determined during the semantic model refresh, such as “current month”, “yesterday”, or “first employee in the complete Employee table when listed alphabetically”. I am not considering default values that depend on other filters applied in a report. In other words, the default value(s) can be determined based on the data present in the model alone, without considering any filters that might be applied in a report.

Semantic model

The model I have used for this post is set up as follows:

The Date table initially contains these columns, including “Offset” columns which contain integer offsets relative to the current year, month or date at time of refresh.

The sample report page I have created looks like this, and on each version of this page I will adjust the “Select Month” slicer.

To help with testing, I have included a Power Query Parameter named MonthsModifier which specifies the number of months to add to “today”. For example, if MonthsModifier = 1 then 7 May 2024 is treated as “today” within the model (rather than 7 April 2024). This allows us to test slicer behaviour when the current date moves from one month to another.

Now read on to see several solutions to this problem!

Solution 1: Add a column with a fixed value for the default selection

One tried and tested solution is to add a column to the Date table containing a special value for the default selection. I have created a column Month Label with the value “Current Month” for dates in the current month. For all other dates, the column contains a text representation of the month/year.

The logic for this column expressed in DAX is below (I actually added it in Power Query):

-- Calculated column in 'Date' table
Month Label =
IF (
    'Date'[Month Offset] = 0,
    "Current Month",
    FORMAT ( 'Date'[Start of Month], "mmm-yy" )
)

For proper sorting, the Sort By column for Month Label should be set to Month Offset or Start of Month.

The Date table surrounding April 2024 now looks like this:

This is how the report page changes when the date moves from April 2024 to May 2024:

Comments on Solution 1:

  • Straightforward to implement.
  • Works with any client tool (not just Power BI).
  • A label such as “Current Month” mixed with months in “mmm-yy” format could be confusing to report consumers unless there is a clear indication of which month “Current Month” represents. In this case it is present in the page title.

Solution 2: Set Group By Columns property for column used on slicer

Solutions 2-4 share a common approach: the slicer actually filters the Month Offset value, but displays a value representing the corresponding month.

In the article Understanding Group By Columns in Power BI, Marco Russo and Alberto Ferrari described how we can use the Group By Columns property of a column to “store a filter by using an alternate value, which represents the key of the entity”.

How is this useful here? We can set the Group By Columns property of 'Date'[Start of Month] to be 'Date'[Month Offset].

When we select “Apr-24” on the slicer during April 2024, Power BI stores this filter as 'Date'[Month Offset] = 0. When the current month becomes May 2024, the report will show “May-24” selected on the slicer, since it is now the month corresponding to 'Date'[Month Offset] = 0.

In other words, the report consumer sees “Apr-24” or “May-24” selected, but Power BI translates this to Month Offset = 0 in both cases!

We can verify this by inspecting the DAX query generated by one of the visuals filtered by the slicer. Here is the DAX query for the card in the lower-left of the page (slightly edited):

DEFINE
    VAR __DS0FilterTable =
        FILTER (
            KEEPFILTERS (
                VALUES ( 'Date'[Month Offset] )
            ),
            AND (
                'Date'[Month Offset] IN { 0, BLANK () }, -- Filter from Month slicer
                AND (
                    'Date'[Month Offset] >= -6, -- Report-level filter
                    'Date'[Month Offset] <= 0   -- Report-level filter
                )
            )
        )

EVALUATE
SUMMARIZECOLUMNS (
    __DS0FilterTable,
    "Sales_Amount", IGNORE ( 'Key Measures'[Sales Amount] ),
    "Sales_Daily_Average", IGNORE ( 'Key Measures'[Sales Daily Average] )
)

The Group By Columns property cannot be set in Power BI Desktop (yet) but can be set in tools such as Tabular Editor (Tabular Editor 3 shown):

This is how the report page changes when the date moves from April 2024 to May 2024:

Comments on Solution 2:

  • Reasonably straightforward to implement using Tabular Editor or another external tool.
  • Works only with Power BI reports (currently) due to limitations of the Group By Columns property.
  • Side effect: Any slicer selection (whether the “current month” or any other month) is stored as a filter on 'Date'[Month Offset].
  • This method doesn’t work well with dropdown slicers (see Page 2b of the Power BI report). The slicer still functions as described above, but the value visible on the slicer remains “stuck” on the old value until the slicer is interacted with.

Solution 2a: Set Group By Columns to the Month Label column

This is an alternative to Solution 2 where we set the Group By Columns property to the Month Label column instead. This means that the initial “Apr-24” filter is stored as 'Date'[Month Label] = "Current Month", but “Mar-24” is stored as 'Date'[Month Label] = "Mar-24".

Using this method, when the date moves from April 2024 to May 2024, “Apr-24” would update to “May-24” (since the selection is stored as “Current Month”), but “Mar-24” would remain as “Mar-24”.

Solution 3: Button Slicer with alternate Labels defined by a measure

The Button Slicer (announced in November 2023) comes with many interesting features allowing flexible formatting and layout of the visual. The particular features that are very useful here are the ability to hide the Values that the slicer is actually filtering, and display alternate Labels.

Here are the steps I followed to set up the Button Slicer:

  1. Place Month Offset in the Button Slicer’s Field well.
  2. In the Format pane, switch Callout values > Values to Off. This hides the Month Offset value.
  3. In the Format pane, switch Callout values > Label to On.
  4. Choose Selected Month Short (a measure that returns the selected month in “mmm-yy” format) as the Label field.
  5. Adjust Buttons > Padding so that the labels appear in the centre of the buttons. This is necessary since the alignment settings don’t apply to Labels when Values are hidden (hopefully this is fixed later).
Selected Month Short = 
FORMAT ( MAX ( 'Date'[Start of Month] ), "mmm-yy" )

This is how the report page changes when the date moves from April 2024 to May 2024:

Comments on Solution 3:

  • Reasonably straightforward to implement.
  • The ability to display hide the Values and specify alternate Labels is limited to the Button Slicer (at the moment).
  • Since a measure is used as the Label, the displayed text can be customized in any way while preserving the underlying filtered value.
  • The Button Slicer has no dropdown mode. Let’s hope a dropdown slicer with the same options is available soon!
  • The Button Slicer does allow images to be displayed, which could be used instead of Labels. I experimented with SVG data URLs (which could be set up to display text in “mmm-yy” format), but the Button Slicer did not appear to accept them (this could well change as it is still in Preview).

Solution 4: Chiclet Slicer with SVG

This solution is a variation on Solution 3.

The Chiclet Slicer is a certified custom visual by Microsoft that allows images to be displayed. We can create an image (using an SVG data URL) that acts as a visual label.

When testing this method, I discovered is that the Chiclet Slicer only displays image data URLs if they are Base64-encoded (see here).

For example, this URL is not accepted:

data:image/svg+xml;utf8,
<svg width='100' height='20' xmlns='http://www.w3.org/2000/svg'>
    <text x='50%' y='50%' dominant-baseline='middle' text-anchor='middle' font-family = 'Segoe UI'>Jan-23</text>    
</svg>

but this URL is accepted:

data:image/svg+xml;base64,
PHN2ZyB3aWR0aD0nMTAwJyBoZWlnaHQ9JzIwJyB4bWxucz0naHR0cDovL3d3dy53
My5vcmcvMjAwMC9zdmcnPgogICAgPHRleHQgeD0nNTAlJyB5PSc1MCUnIGRvbWlu
YW50LWJhc2VsaW5lPSdtaWRkbGUnIHRleHQtYW5jaG9yPSdtaWRkbGUnIGZvbnQt
ZmFtaWx5ID0gJ1NlZ29lIFVJJz5KYW4tMjM8L3RleHQ+ICAgIAo8L3N2Zz4=

I added Base64-encoded data URLs in Power Query in the column ‘Date'[mmm-yy SVG Base64 URL]:

I then added Month Offset in the Category field well, and mmm-yy SVG Base64 URL in the Image field well:

This is how the report page changes when the date moves from April 2024 to May 2024:

Comments on Solution 4:

  • Encoding SVG images in Base64 feels cumbersome, and in this case adds a “large” text column to the Date table.
  • This method may be useful if specific formatting features of the Chiclet Slicer are important.

Solution 5: Preselected Slicer

The Preslected Slicer is a certified custom visual that allows dynamic default selections that depend on the filter context. This visual requires a Pre Selection measure to be provided. This measure must return Boolean (True/False) values, and is evaluated for each value of the field displayed on the slicer. Values for which the Pre Selection measure returns True are treated as default values.

The slicer also requires a “dummy” column to be provided in the Dirty Status field well. This column is required for technical reasons and must be a Boolean column containing both True and False values.

I created the measure Current Month Indicator to act as the Pre Selection measure:

Current Month Indicator = 
SELECTEDVALUE ( 'Date'[Month Offset] ) = 0

I also created a table Dummy for Preselected Slicer containing a single column:

I then set up the Preselected Slicer as follows (both dropdown and list are possible with Preselected Slicer):

As expected, “Apr-24” is selected initially. If the selection is changed by the report consumer, the Preselected Slicer becomes “dirty” and the Reset icon in the header must be clicked to return to the default selection.

This is how the report page changes when the date moves from April 2024 to May 2024:

Comments on Solution 5:

  • Since the Preselected Slicer uses a measure to determine the default selections, it can handle default selections determined during model refresh as well as default selections determined based on filters in the report.
  • The need for the Dirty Status column means that table(s) containing one column per Preselected Slicer need to be added to the model (if there are multiple present on a page).
  • When in dropdown mode, the Preselected Slicer does not suffer from the problem of Solution 2 when used with dropdown slicers, where the value displayed on the slicer remains “stuck” on the previous value.
  • The appearance of this slicer might not be for everyone. Formatting options are more limited than some of the other slicer visuals.

Summary

  • Download the PBIX for this post here!
  • Various methods are available to set a default slicer selection that updates when the semantic model is refreshed, using both Core and Custom Visuals.
  • In my opinion the most interesting are Solution 2 (Group by Columns) and Solution 3 (Button Slicer).
  • Updates by Microsoft are expected for the core slicer visuals, potentially including functionality similar to the Preselected Slicer where defaults are determined by a measure.
  • While I used “current month” as an example, the methods in this post can be applied to columns of any type, and can involve multiple selection.
  • I am sure there are other interesting and creative methods available, and other interesting applications of Group By Columns. Please share your thoughts in the comments!

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

All the best,
Owen