cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Setting the Default Value of a Slicer

The client has data which is updated monthly and the dashboard contains a chart and a slicer which is set to the current month. They would like the slicer to change the default selection when the data for the new month is loaded.  For example, the current month selected in the slicer is January, and they would like the month selected change to February when that datat is loaded so the corresponding chart will show the data for the latest month.

 

I am drawing a blank as to how you would make this happen. Can someone point me in the right direction?

1 ACCEPTED SOLUTION

Accepted Solutions
Resident Rockstar
Resident Rockstar

You can't do this directly in the report designer, but you should be able to do some modelling to make it work. See the sample .pbix file here.

 

Basically you make a new field based on the following bit of Power Query M:

if Date.IsInCurrentMonth( [Date] )
then "Current Month"
else [Month]

Then you set the slicer to "Current Month", when the month changes and the dataset is refreshed, the slicer's "Current Month" selection can remain the same. What "Current Month" actually refers to has changed, but not the literal slicer selection.

 

As a bonus, you get a sneak peek at a few snippets from my personal work-in-progress ultimate date table in that .pbix. I'll be publishing the full version once I finalize.

View solution in original post

26 REPLIES 26
Resident Rockstar
Resident Rockstar

You can't do this directly in the report designer, but you should be able to do some modelling to make it work. See the sample .pbix file here.

 

Basically you make a new field based on the following bit of Power Query M:

if Date.IsInCurrentMonth( [Date] )
then "Current Month"
else [Month]

Then you set the slicer to "Current Month", when the month changes and the dataset is refreshed, the slicer's "Current Month" selection can remain the same. What "Current Month" actually refers to has changed, but not the literal slicer selection.

 

As a bonus, you get a sneak peek at a few snippets from my personal work-in-progress ultimate date table in that .pbix. I'll be publishing the full version once I finalize.

View solution in original post

What a clever idea. Thanks for the tip, I never would have thought of that.  Sending the code was most appreciated too.

This also seems like the right solution for me - and thanks for your answer to my other post ....but I can't follow where/how to paste the code and can't access your pbx file. is there an online Power BI tutorial you can point me to where I can see it illustrated? If i paste your formula into the formula bar for a new column it appears to have an issue with the square brackets. thanks again.

Resident Rockstar
Resident Rockstar

@jbowler, the code I shared is from Power Query, which you can access using the Edit Queries ribbon menu item. There's a self-paced edX course designed by Microsoft, with its own forum in this community.

If you publish this report to power BI service while the current month in the slicer is selected then open the report form the power BI report which you just published to https://msit.powerbi.com after logging in to it. Then the slicer is not visible .You cannot see any of the data but if you click  on "..." and export to CSV you will see there is data but the slicer with selected value will not visible at all. What is the workaround to fix such kind of issue?

Any idea on this is appreciated. Here is the print screen

before.pngAfter.png

 

placeholder.png

 

Regular Visitor

Hi all, 

I am hoping someone is able to help me.  I have a report that contains mutiple slicers to enable the user to select a given year, month and a specific day.  I have used the solution suggested by Greggyb within this thread which has worked brilliantly to give me the 'Current Month'.  However, I now need to do the same thing so that the slicer will default to the current day.  I have tried mutilpe things but am drawing a blank.  I'm just wondering if anyone else has tried to do something similar or if I'm wasting my time and there is another solution I have missed.  I would be grateful for any suggestions and thanks in advance

Advocate I
Advocate I

I have the same problem. I also need to set the default value for Today's Date when opening the Dashboard/Report. 

*Link Added*

Refer to this post for a better solution using DAX Current Month Using DAX.

 

 

Thanks,

Fahd

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors