How to dynamically select the default values we need in a slicer in Power BI

[v-kongfanf-msft]

Scenario:  

In this article, we would like to realize how to dynamically select the default values we want in a slicer. For this , we need to set the last three months in the slicer visual by default when we open our report. 

Expected Result: 

Expected Result 1: 

Expected Result 2: 

Sample Data: 

In the sample data, there is one table.  

The test data is like this:   

We need to put the [Date] as a slicer , and we need to dynamically select the last three months in the table which is compared with the max date in the table. 

How: 

Here are 2 solutions.  

Solution 1: 

We need to click "New column" in our table to create a calculated column, it is used to define what rows are the last three months and other are just show the Date type we want , like this: 

Date 2 =
VAR _max_date =
MAX ( 'Table'[Date] )
VAR _last_three_month =
EOMONTH ( _max_date, -3 ) + 1
RETURN
IF (
[Date] >= _last_three_month,
"Last three month",
FORMAT ( [Date], "yyyy/mm" )
)

Then we can get this calculated column.

Then we put the [Date 2] calculated column on the slicer visual and we need to select the "Last three month" so that we can dynamically filter the data. 

Solution 2: 

If we want to realize the default preselect of the Date and not show the "Last three month" , just show the raw data in the table (2022-09-01,2022-10,01,2022,11-01), we need to select other data and click "reset", it can also be reset to the original state. 

For the slicer that comes with Power BI, we cannot get it. But we can get the three points in the "Visualizations", please click the "Get more visual".

Then we find the "Preselected Slicer" and add the visual to our Power BI Desktop. 

The "Preselected Slicer" feature in Power BI allows you to determine which values users must select when opening a report based on another column or measure. After obtaining these values, they are set as the default state. Importantly, the model won’t modify the report every time default selections need to be changed. 

Please notice that "Preselected Slicer" is a non-Microsoft custom visual. There is possibility that a non-Microsoft custom visual goes wrong or becomes charged in the future. It’s out of MS support scope. For more detailed information, you can refer to the relevant documentation on this visual object here.

Preselected Slicer

Then we can put the visual in our report page, and we can see this visual need three fields: 

(1)Fields: The field we show on this slicer visual. 

(2)Pre Selection: We can put the measure we created here to judge which data we need to preselect by default. 

(3)Dirty Status: We can put a column which have the value return in our measure , like TRUE() and FALSE(). 

First , we need to create a table with one column , and the value is True and False, like this.

Second, we can create a measure to define which data we need to preselect by default. If we need to preselect the data by default, then we can return True or return False. 

Flag =
VAR _date =
SELECTEDVALUE ( 'Table'[Date] )
VAR _max_date =
MAXX ( ALL ( 'Table' ), [Date] )
VAR _last_three_month =
EOMONTH ( _max_date, -3 ) + 1
RETURN
IF ( _date >= _last_three_month, TRUE (), FALSE () )

Then we can put the 'Table'[Date] in the Fields , [Flag] in the Pre Selection, 'Dirty Table'[Dirty Status] in the Dirty Status, like below screenshot.

Then we can preselect the last three months in the slicer visual dynamically by default , and when we select other date, we just need to click the red reset button in the upper right corner to recover the default selections.

Summary: 

The built-in slicer in Power BI cannot currently achieve default selection of specific dates, but we can create a calculated column to customize rules for specifying default dates or use a custom visual object called “Preselected Slicer.”

Author: Yang Liu 

Reviewer: Ula and Kerry 

How to do dynamic select the slicer we need.pbix