Setting the Default Value of a Slicer

[desertislesql]

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?

[Anonymous]

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?

[GilbertQ]

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"

[xonde]

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.

[Marvhall]

I know this is a few years old but the Watermark on the "Power Slicer" makes this app useless in a professional business manner. 

[xonde]

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]

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. 

[Anonymous]

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

[felyoubi]

*Link Added*

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

Thanks,

Fahd

[FrugalEconomist]

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

We have a dashboard that is warehoused weekly.

[GilbertQ]

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.

[KHorseman]

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

[AndrewDang]

Thanks @KHorseman and all for your help;

I was able to use this to fix my issues.

Thanks;

Andrew

[felyoubi]

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]

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

[felyoubi]

Haha, Thanks

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

-Fahd

[SK_79]

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 · Accepted solution]

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.

[Anonymous]

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])

[harib]

@greggyb 

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

Many Thanks 

[chucksteel]

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) )