Do you need to set the default slicer value in your Power BI dashboard?

Power BI is an exceptional visualization tool that offers numerous benefits, which includes the ability to create dynamic and interactive reports. This is made possible by its various features, such as its powerful visualization tools and the Power Query integration that allows users to design custom solutions using the M language.

Among the popular Power BI visualizations is the slicer, which you can use to filter and slice data based on specific criteria. While slicers are incredibly useful for data analysis, there are times when you want the slicer to have a default value that updates with new data.

Thankfully, there is a solution to this problem: setting a default slicer value. Although this is not natively available on the report page, having your slicer set to a default value can be achieved using the M language in Power Query.

By doing this, your slicer can have a default value that automatically changes as new data is uploaded. This article will guide you through the process of setting default slicer values in Power BI, which can significantly enhance your data analysis efficiency and save you time.

Set Default Slicer Value for Date Table

As an example, a default slicer will be set on the date table of the dataset, which will ensure that the data is automatically updated when new data is uploaded. But first, a dynamic date table must be created.

Creating a Dynamic Date Table

A dynamic date table is a popular choice over a static one because it automatically expands, and updates as new data is added. Power BI offers various methods for creating a dynamic date table, and one of them is outlined below.

Step 1: Create the StartDate Column

To create a date table from scratch, go to the Power BI menu, click on Get data and select Blank query to open a blank query.

= Date.StartOfYear(List.Min(#"Sales by Store"[transaction_date]))

This will take you to the Power Query editor where a blank query with the name Query1 is open. Rename the query as DateTable by double-clicking on the query name and pasting the above syntax into the formula bar.

The syntax retrieves the earliest transaction date from the Sales by Store table and returns the first day of the year for that date.

Breaking it down:

• The List.Min function retrieves the earliest transaction date from the Sales by Store table. The #”Sales by Store” is a reference to the Sales by Store table in the Power Query editor, with the pound sign (#) indicating that it is a table reference.
• [transaction_date] specifies the name of the column in the Sales by Store table that contains the transaction dates.
• The Date.StartOfYear function returns the first day of the year for the earliest transaction date.

The syntax returns 01-Jan-17, the earliest date in the transaction date column in the Sales by Store table.

Convert the result to a table. To do this, click on the To Table dropdown and select To Table.

The DateTable query now has one column that contains the values generated from the syntax. Rename the column as [StartDate] by double-clicking on the column name.

Step 2: Create EndDate Column

To create the [EndDate] column, go to Add Column and select Custom Column.

Date.EndOfYear(List.Max(#"Sales by Store"[transaction_date]))

In the Custom Column window, paste the syntax above into the Custom column formula box. Assign a name to the column using the New column name bar. Click OK when you’re done.

The syntax is similar to the first one used to create the StartDate column, but instead of returning the first date in the [transaction_date] column of the Sales by Store table, this syntax returns the last date.

The query does this by using the List.Max(#”Sales by Store”[transaction_date]) formula to return the maximum date in that column. The Date.EndOfYear() formula then calculates the end of the year date from the result of the List.Max formula.

When you click on OK, the syntax will return a new column with the value “31-Dec-19”.

Convert both columns to date format by clicking on the format icon beside the column name and selecting Date.

Step 3: Create the Dates Column

{Number.From([StartDate])..Number.From([Endate])}

To do this, open the Custom Column dialogue box and paste the above syntax. The syntax will create a list of numbers that begins from the value in the [StartDate] column and ends at the value in the [Endate] column.

The formula breakdown:

• [StartDate] and [EndDate] are the names of the columns that contain the start and end dates respectively.
• The Number.From function is used to convert the dates in the [StartDate] and [EndDate] columns into corresponding numerical values.
• The “..” operator creates a range between the numerical values of [StartDate] and [EndDate]. The entire formula is enclosed in curly braces { } to indicate that it is a list.

After you click OK in the Custom Column window, a new [Dates] column will be created with an expandable list of values.

After creating the [Dates] column, right-click on the column name and select the Remove Other Columns option to delete the [StartDate] and [Endate] columns because they’re not needed anymore.

Click on the expansion icon in the column name and select Expand to New Rows.

After expanding the list, the column will contain a list of numbers, which you can convert to date format by right-clicking on the column header and selecting Date.

The converted dates will range from “01-Jan-17” to “31-Dec-19” which is the range of the dates available in the Sales by Store table. Also, because the [Endate] column is based on the maximum date in the [transaction_date] column, it will dynamically expand to include new dates added to the data source.

Step 4: Create Other Columns

In this step, you can create other columns from the [Dates] column based on what’s needed for your analysis. For this illustration, the [Month], [Month Name], [Current Month], and [Month Slicer] columns will be created.

All columns will be created from the Custom Column option in the Add Column menu.

Date.ToText([Dates], "yyyy MMM")

To create the [Month] column, paste the syntax above into the Custom column formula bar. The syntax extracts the year and month from the Dates column and displays them in the year and month format.

Here’s a breakdown of the syntax:

• Date.ToText(): This function converts the date value to a formatted text string.
• [Dates]: The name of the column containing the dates to be converted to text format.
• “yyyy MMM”: The text string that specifies the format of the output. “yyyy” indicates the year in four digits, and “MMM” indicates the abbreviated name of the month (e.g., Jan, Feb, Mar). The space between “yyyy” and “MMM” will separate the year and the month in the output.

When you’re done, the [Month] column will be created, and it will contain the year and abbreviated month name as specified in the syntax.

To create the [Month Name] column, click on any row in the Dates column, go to the Add Column menu, click on Date, then Month, then Name of month.

This will extract and return in a new column the month name of every date in the [Dates] column.

The next step is to create a [CurrentMonth] column that will check if the date in the [Date] column is in the current month based on your computer’s time and date settings and return a true or false.

Date.IsInCurrentMonth([Dates])

To do this, paste the syntax above in the Custom column formula bar. Change the column name to Current Month. Click on OK when you’re done.

When this is done, you will have created a new [Current Month] column with true or false values.

When you look closely, you will notice that dates before and after May of 2017 all return FALSE. The Date.IsInCurrentMonth function works with the date settings on your computer. For this reason, all dates that are not in May of 2017 will return false because the computer’s date is set to May of 2017.

With this formula in place, the months that will return true values in the Current Month column update automatically as the date changes.

if [Current Month]
then "Current Month"
else
[Month]

In the next step, you create a [Month Slicer] column. This is the column that will go in the slicer visual. The syntax above is an if expression that returns “Current Month” if the value in the [Current Month] row is true and returns the corresponding [Month] value if the [Current Month] value is false.

After pasting the syntax, click on OK.

The [Month Slicer] column is now ready, and it looks almost exactly like the values in the [Month Column] at first glance.

However, the similarity between the Month and Month Slicer columns ends when it gets to the point where the value of the current month is being checked based on the Date.IsInCurrentMonth formula is used to create the Current Month column.

As such, the Current Month column and the Month Slicer column are dynamic. Basically, the Month Slicer column is the readable version of the Current Month column because it’s much easier on users that a slicer uses “Current Month” to refer to the values of the current month than True/False.

Click on Close & Apply to load the query into Power BI.

Step 5: Create a Relationship between DateTable and Sales by Store Table in Data Model

After creating the DateTable, you need to create a connection between the table and the Sales by Store table. This is so that filters from the DateTable can propagate to the Sales by Store table. Without this, any slicer created using values from the DateTable will not work on the Sales by Store table.

Different approaches exist for creating connections in Power BI models. The simplest involves dragging and dropping one column from the “one” side of the relationship directly on top of the column with the “many” side of the relationship.

In this case, the [Dates] column has been dragged and dropped directly on top of the [transaction_date] column, creating a one-to-many relationship between both tables.

Step 6: Create Visual

Now that the model is properly set up, the slicer can be created using the [Month Slicer] column from the DateTable.

Assuming a line graph is created that shows the progression of sales for each month, creating a slicer using the [Month Slicer] column will control the months that are in view.

This graphic shows a list of months and one stating “Current Month”. When the slicer is set to the “Current Month” value, the graph displays data for quantity sold in that current month based on the computer’s time and date. As such, the slicer will filter the line graph in the report so that it returns only values data for May 2017 since that’s the current month.

Now that the current month has changed, the graph has also changed because the “Current Month” value in the slicer now refers to a new month, which is June 2017.

This shows that you don’t have to do anything to the report for it to change. As long as the “Current Month” value is selected on the slicer (the default value), the graph will update because “Current Month” is dynamic and will always refer to the current month based on the time and date settings of your computer. And by doing so, you have yourself a default auto-update for your data on every refresh.

Conclusion

Creating default slicer values in Power BI can significantly improve the efficiency of your data analysis by saving time and reducing repetitive tasks. With the help of Power Query’s M language, it’s possible to create values that change automatically as new data is uploaded and set them up so you can use it as the default values for your slicer.

By following the step-by-step guide provided in this blog post, you can easily set default slicer values in your own Power BI reports; thereby, ensuring you spend less time on data preparation and more time on analysis, allowing you to make more informed business decisions.