cancel
Showing results for 
Search instead for 
Did you mean: 
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

26 REPLIES 26
Mukovnin
Regular Visitor

Hi all!

What if I want to month with data will be checked by default after PQuery update. And a month without data just will be shown in the filter? Is it possible?

Untitled.png

HI there

Currently that is not possible because when you select something it will keep that month selected.

What would work better is to rather use a value that does not change each month such as "Current Month"




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

xonde
Frequent Visitor

GOOD NEWS!! The Power BI March 2019 Update just released a custom visual called the "Power Slicer" you can import this visual from the marketplace. This visual has a million more options than the normal slicer and more importantly allows you to set a default value. This can be done by going into the format tab under the "selection" options and setting the default value to a literal in your list.

For example if you had a power slicer with a bunch of years in it:
.2015
.2016
.2017
.2018
.2019

 

You can set "Default Selection" to be "2019"(minus the quotes). But if you want it to be dynamic and always show the current year, then even more good news, this options accepts javascript, so you can set the "Default Selection" to "(new Date()).getFullYear"(minus the quotes)  and the Default will always be the current year. If you have a more specific need you can always google it for some javascript help, and someone on StackOverflow will probably have your answer.

 

Heres a link to the video (Skip to 14:43): https://www.youtube.com/watch?v=rBPGH6eYlT0&feature=youtu.be&t=10

Hope this helps,

Xonde.

xonde
Frequent Visitor

GOOD NEWS!! The Power BI March 2019 Update just released a custom visual called the "Power Slicer" you can import this visual from the marketplace. This visual has a million more options than the normal slicer and more importantly allows you to set a default value. This can be done by going into the format tab under the "selection" options and setting the default value to a literal in your list.

For example if you had a power slicer with a bunch of years in it:
.2015
.2016
.2017
.2018
.2019

 

You can set "Default Selection" to be "2019"(minus the quotes). But if you want it to be dynamic and always show the current year, then even more good news, this options accepts javascript, so you can set the "Default Selection" to "(new Date()).getFullYear"(minus the quotes)  and the Default will always be the current year. If you have a more specific need you can always google it for some javascript help, and someone on StackOverflow will probably have your answer.

Hope this helps,

Xonde.

Anonymous
Not applicable

Inner filter (a copy of) your big enought calendar table with your data table. Add a column like YEAR*10000+MONTH*100+DAY and erase all rows but the last one and you will get a ONE ROW TABLE.

 

Make a DAX expresion like if(isfiltered(A),(if(hasonevalue(A), selectedvalue(A), ¿? ), <one reference here to the one row table that you want to make default>)       

 

A, for example can be the real calendar, or the country list, or the state list or whatever you want to make default behaviour.

 

When you refresh your data, your ONE ROWS TABLES will change, so your defaults also will change. 

 

 

Dinu
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

Is there a way to tweak this to show most recent date?

We have a dashboard that is warehoused weekly.

Hi @FrugalEconomist

 

You can do this using a DAX measure called LASTDATE, or you can do it in the Query Editor and create a table which will only have the MAX date. In order to do that your data type on your column has to be DATE, and then when you filter it will give you the option for the Earliest or Latest.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

KHorseman
Community Champion
Community Champion

@felyoubi If you want to create the same month column using DAX in the table editor, the column formula would be

 

Month Name = IF(MONTH(DateTable[Date]) = MONTH(TODAY()) && YEAR(DateTable[Date]) = YEAR(TODAY()), "Current Month", DateTable[Month])

 

And for the current day it would be

 

Day = IF(DateTable[Date] = TODAY(), "Today", DateTable[Date])

 

...assuming the prior existence of two columns called Date and Month obviously.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks @KHorseman and all for your help;

 

I was able to use this to fix my issues.

 

Thanks;

Andrew

It's a good additions to have but in my case it is useful if you want to see how well you're performing historically month by month. If I need to see just the current month then I use a filter.

 

I appreciate the feedback

KHorseman
Community Champion
Community Champion

@felyoubi were you not asking for the equivalent DAX formula then? Your previous post was oddly worded so I'm not sure what you were asking for.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Haha, Thanks

I forgot to add the link. Post updated and I do appreciate your feedback Smiley Happy

 

-Fahd

SK_79
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

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

Anonymous
Not applicable

Hi Greg,

I have a similar issue. I hope you can help me with this as I'm relatively new to power BI.

I have an "input date" column which has DateTime values(the format is "m/d/yyyy hh:mm:ss AM").

Within this column, I have a date value(eg 1/1/1970) that fetches historical data and all other date values current to the previous 2 years.

My requirement is when I use this "Input_date" field in a slicer I need to have the historical date column to be always preselected and the latest date(max date) to be preselected in the slicer. So when I open the report say after a month from now the slicer should by default select and show the historical date (1/1/1970) and the latest date(which is next month). Is this possible? Would you be able to help me with the logic, please? I am using the below logic which doesn't work though.

 

Would you also be able to suggest a solution if the Date column were a text datatype with the date values in it?

 

if [input_date] = #datetime(1,1,1970,12,00,00)
then "Historical data"

else if [input_date] = List.Max([input_date])
then "Latest date"
else [input_date])

@greggyb 

 

Can u please provide the sample file. The one which u have provided the link is not working.

 

Many Thanks 

 

I created a variation of this that creates the table using the current date and the previous 365 days. Thanks to @greggyb for the original solution. This has been super helpful.

 

In the original code the source of the table is this:

= List.Dates(
  #date(2015,1,1)
  ,Duration.Days( #date(2016,12,31) - #date(2015,1,1) ) + 1
  ,#duration(1,0,0,0) )

I used this code instead:

= List.Dates(
  DateTime.Date(Date.AddYears(DateTime.LocalNow(), -1))
  ,365  ,#duration(1,0,0,0) )

@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!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors