Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
desertislesql
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
greggyb
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

27 REPLIES 27

@greggyb

hi!

 

great idea!

is there any way to show both "Current Month" and the value of the month?

i want the default selection will be 'Last 4 weeks' but i want to let the user the option to select the last one week only,

in this way it's not available

 

thanks!

@greggyb hi is there any way thru which i can get last day,

 

in my data file last  date is 10 june where as in date table its showing 18 july 2016.

 

Also Please let me know how can i make last day as a default day like i said 10 jun was the last date in my data table so how come i put it to default date.

 

we have  current month, year ,week. function in power query but we dont have current day function.

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.

@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

 

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.