A solid technique for sure. However, what a lot of people are looking for is a way to change the actual slicer selection upon refresh. For example, imagine a model is updated weekly, and there is a slicer set to filter on the latest week (i.e. "week ending mm/dd/yyyy"). When the report is updated, the slicer for week does not "change itself" to the newest week; users are still required to make that change. Thanks for all your contributions to the community!
The technique shown is appreciated but like you say what you really want to be able to show your users is which item is the slicer is the default. Ie in the example in the video if Current Month is used when nothing selected in the slicer then you'd want it shown to the user as being selected otherwise the user does not now in any easy manner what is being selected. Dates are a perfect example where you want it to defualt - in the slicer - to a particular date without forcing people to select it everytime they load the report
Yes, that is what I am looking for as well. For a report the current reporting year must be selected in the slicer. In februari the report must show the data for the new reporting year and I was hoping for a solution to set the new reporting year selected in the slicer. Modifying all measures to default to a certain year is not always desirable.
Same here. I want my slicer to show the selected default values. So if I have a default of the last 4 months, my slicer should show that the last 4 months are selected.
This measure here is a pure gold, thanks a lot! Can be used for any period, week, year with a little tweak For my task - get variable filter for each month of 3 years i only need to + YEAR - YEAR * 12 Var. filter = IF( MONTH('Date'[Date]) = MONTH(TODAY()) && YEAR('Date'[Date]) = YEAR(TODAY()), 0, MONTH(TODAY()) - MONTH('Date'[Date]) + ((YEAR(TODAY()) - YEAR('Date'[Date])) * 12) )
This is indeed cool; however, what most of us wish is to **visually** have the slicer change to a default value. With this trick, it can be confusing for the user since they don't know the actual value filtered in the visualizations.
I couldn't get the second IF statement to work with dates (results just showed blank), but it worked once I replaced it with a Filter expression. Either way, I've been looking for this solution for a while now. Thanks so much for sharing!
Good video ... Remembered me of my old Excel days, but it seems you are missing the point. What people in all those forums mean is: click on a slicer value, hit save, exit and every time the dashboard is opened, this saved selection is there! That is the stuff which drives people crazy, not that you cannot prefilter (which is basically what you are doing here) the data which is affected by those slicers! In 2021 power bi still cannot do the simplest things as storing column widths (permanently no matter of which slicer...), Store default drop-down selection, get proper formatting etc. Etc.
This does not "set a default slicer value"; this only filters data based on slicer (or simulate click on "Current Month" when nothing is selected), but slicer is not setup automatically based on some data. I'm looking for solution: when new day is processed in DWH, I need slicer to be automatically switched for that new date.
I've noticed that a few vids like this, which claim to show you how to set a default slicer value but actually show you how to cope with a slicer which has no selected value. If you make a slicer's Selection Controls to Single Select, it will create a default value but it doesn't give you control over what that default value is.
if i'm not mistaken, this just "defaults" to sum certain rows; it doesn't really filter your data unless you've already kind of pivoted everything into a single table and it's all numerical data. otherwise, it's just a ginned-up sumif expression which makes it useless for folks trying to set a default filter on appended tables/datasets based on source.name. could be mistaken tho.
+J K The point of this demonstration is to highlight the method of identifying if a selection has been made on a slicer. This will default to a certain calculation depending on if a selection or no selections are made. You can make it as complex as you like. In this example, the calculation is pretty easy everything is on one table.
You're not taking into account that your end users may want to see all 4 months worth of data. You're assuming that if all boxes are selected to just display the current month.
Thanks for the video. But I am confused: if we selected all or none, the logic goes to the second IF, how can the expression SelectedValue(data[daterange]) = "Current Month" be true, it should return Blank(). Anyone could help?
I have data from Jan/01/2021 to up to current date and also future dates in my date table. I want to show the current month as the default Month in slicer dynamically ( whenever we are getting the latest data)
what if the column we want defaultselection for is not a date column rather what if it's a text column. If column has value like '2020-21'. Is it possible to do it for text column??
This works great IF you've got only a few different visuals... But Is there a way to set a default slicer for the entire report? (not just specific visuals) - I've got dashboards with literally 40-50 visuals spanning a few pages.
Hello great video. I tried working on this but the date range is giving me wrong "Month(s) ago" values since there are data from last year. How do I deal with this?
Good one! but it should be like if all filters are selected then it should show all data instead of current month..and if none is selected then current month data..?
All and none are the same thing here! It’s unfortunate but I haven’t found a solution that can distinguish between all/nine selected since the default slicer functionality dictates that all values are selected when none are selected.
Hello, is there a similar workaround to default a slicer based on user log in? The RLS settings work fine, but when the dashboard is accessed the slicers are not automatically selected. What I was hoping to do is: If user 1 logs in, this user should automatically see his or her own data and the slicer is automatically selected. But based on user 1’s role, he/she should still be able to see other options in the slicers. What I am having issue now is when user 1 logs in, this user can see the options relevant to him/her but the slicer is not automatically selected by default so the visuals still show blank. Appreciate your advice. Thanks in advance!
Same here. How to select a a location slicer value from table of user and location as the default slicer? And the kicker, direct query on D365 Dataverse. Why is this so hard? :)
Hey Anatoli, you're right and I've recently started changing how I do this. I use the following formula: DATEDIFF(Table1[Date],DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())),MONTH)
@@BIElite Thanks. I used a similar logic to get the selected date first with (this will return the last day of the month from the slicer): SelectedDate = EOMONTH(DATEVALUE([SelectedSummarisedYear] & "-" & [SelectedSummarisedMonth] & "-01"), 0). Then SelectedDateMinusN_Mths = EDATE([SelectedDate], -[Parameter Value]) will return the number of months back in the "what if parameter".
Good question Florencia... you could definitely throw in a && but I think you'd also have to write some more code to determine if this second slicer has a selection or not. Are you going to be showing this second slicer on the page?
I have a similar question. I have 1 slicer, but about 6 different "metrics" that I am displaying in 6 different cards. This means, I need a measure for each of them, since they all use different data. Is there a better approach for this? Thanks!
Actually I have a slicer as a Status which has Active, Inactive and closed. I want to set the default value as active. I understand your approach works good for Date fields but its not working for String field. Hope this is clear
@@sripriyabs yes, I'm wondering the same. I have a map on which I'd like the default country displayed (i.e., no filters selected) to be USA unless the COUNTRY filter is anything other than USA.
Hey Thanks Bro, Filtered google to get here and this is close, so can I ask or any expert here some questions. I have a scenario 1. Status that I am set for slicer that has huge list. I cannot use this as this requires aggregate function to be given as must, but I cannot do aggregation and it is cribbing FTODefaultTest = If ( COUNTROWS(DISTINCT(ALLSELECTED(_Work[Status]))) < COUNTROWS(DISTINCT(ALL(_Work[Status]))), MIN(_Work[Status]), /* I cannot have MIN on values returned by system to select, I am not calling a measure. */ If ( SELECTEDVALUE(_Work[Status])= " Scheduled", MIN(_Work[Status]), BLANK() ) - I get blank, how do we fix this, 2. I want to set multiple options as default 3. Similarly i need to set 3 FY years from current year as default Any help?
Inspiring video, thanks .. I found it when i was looking for a solution for my case, which I have an event calendar table and want to show one event (let's say event A) with each other single events, so it is more like event A should always be in the calendar with the other event(s) which is (are) selected by the user. I appreciate any suggestion/help. Thanks :)
Ah that's a super interesting scenario... I think you will be able to do this using a very similar method to this video. I can't think of the exact solution right now, but if you have a sample PBIX that you could send me, I'd love to take a crack at it. Email: powerbielite@gmail.com
A solid technique for sure. However, what a lot of people are looking for is a way to change the actual slicer selection upon refresh. For example, imagine a model is updated weekly, and there is a slicer set to filter on the latest week (i.e. "week ending mm/dd/yyyy"). When the report is updated, the slicer for week does not "change itself" to the newest week; users are still required to make that change. Thanks for all your contributions to the community!
The technique shown is appreciated but like you say what you really want to be able to show your users is which item is the slicer is the default. Ie in the example in the video if Current Month is used when nothing selected in the slicer then you'd want it shown to the user as being selected otherwise the user does not now in any easy manner what is being selected. Dates are a perfect example where you want it to defualt - in the slicer - to a particular date without forcing people to select it everytime they load the report
Yes, that is what I am looking for as well. For a report the current reporting year must be selected in the slicer. In februari the report must show the data for the new reporting year and I was hoping for a solution to set the new reporting year selected in the slicer. Modifying all measures to default to a certain year is not always desirable.
@@brainpowersolutions4472 did you find a solution for this?
Same here. I want my slicer to show the selected default values. So if I have a default of the last 4 months, my slicer should show that the last 4 months are selected.
This measure here is a pure gold, thanks a lot! Can be used for any period, week, year with a little tweak
For my task - get variable filter for each month of 3 years i only need to + YEAR - YEAR * 12
Var. filter =
IF(
MONTH('Date'[Date]) = MONTH(TODAY()) && YEAR('Date'[Date]) = YEAR(TODAY()),
0,
MONTH(TODAY()) - MONTH('Date'[Date]) + ((YEAR(TODAY()) - YEAR('Date'[Date])) * 12)
)
This is indeed cool; however, what most of us wish is to **visually** have the slicer change to a default value. With this trick, it can be confusing for the user since they don't know the actual value filtered in the visualizations.
I couldn't get the second IF statement to work with dates (results just showed blank), but it worked once I replaced it with a Filter expression. Either way, I've been looking for this solution for a while now. Thanks so much for sharing!
Good video ... Remembered me of my old Excel days, but it seems you are missing the point. What people in all those forums mean is: click on a slicer value, hit save, exit and every time the dashboard is opened, this saved selection is there! That is the stuff which drives people crazy, not that you cannot prefilter (which is basically what you are doing here) the data which is affected by those slicers! In 2021 power bi still cannot do the simplest things as storing column widths (permanently no matter of which slicer...), Store default drop-down selection, get proper formatting etc. Etc.
Great solution! Thank you so much!
This does not "set a default slicer value"; this only filters data based on slicer (or simulate click on "Current Month" when nothing is selected), but slicer is not setup automatically based on some data. I'm looking for solution: when new day is processed in DWH, I need slicer to be automatically switched for that new date.
I've noticed that a few vids like this, which claim to show you how to set a default slicer value but actually show you how to cope with a slicer which has no selected value. If you make a slicer's Selection Controls to Single Select, it will create a default value but it doesn't give you control over what that default value is.
if i'm not mistaken, this just "defaults" to sum certain rows; it doesn't really filter your data unless you've already kind of pivoted everything into a single table and it's all numerical data. otherwise, it's just a ginned-up sumif expression which makes it useless for folks trying to set a default filter on appended tables/datasets based on source.name. could be mistaken tho.
+J K The point of this demonstration is to highlight the method of identifying if a selection has been made on a slicer. This will default to a certain calculation depending on if a selection or no selections are made. You can make it as complex as you like. In this example, the calculation is pretty easy everything is on one table.
How did you create the month's tabs?
Great video!
You're not taking into account that your end users may want to see all 4 months worth of data. You're assuming that if all boxes are selected to just display the current month.
That would be like a special requirement from the final user, I got it for example LOL
Thanks for the video. But I am confused: if we selected all or none, the logic goes to the second IF, how can the expression SelectedValue(data[daterange]) = "Current Month" be true, it should return Blank(). Anyone could help?
That's what I get too... ???
This would give the defult:
CALCULATE(
SUM(Data[Amout]);
Data[DateRange] = "21 Month(s) Ago"
)
What a Great Video..Amazing DAX logics. Thanks a lot!!
Thanks Pratik! Sorry for the late response
I have data from Jan/01/2021 to up to current date and also future dates in my date table. I want to show the current month as the default Month in slicer dynamically ( whenever we are getting the latest data)
Great! I had a similar problem, and thanks to you my problem is solved. Thanks a lot! ;)
Thanks for watching, Helder!
Hi
little confused on second if part...when nothing is selected how does selectedvalue DAX return "Current month"
How is current month returned ? instead all values are selected by default in a slicer. I tried implementing this but didnt work for me.
what if the column we want defaultselection for is not a date column rather what if it's a text column.
If column has value like '2020-21'. Is it possible to do it for text column??
do you ahve a video how to make that exact slicer ? Thx
How do i do it for the days in a month? How do i write the dax for it? Sorry. Pretty new to this.
This works great IF you've got only a few different visuals... But Is there a way to set a default slicer for the entire report? (not just specific visuals) - I've got dashboards with literally 40-50 visuals spanning a few pages.
How did you get the Amount column?
Hello great video. I tried working on this but the date range is giving me wrong "Month(s) ago" values since there are data from last year. How do I deal with this?
+Neal Virtudes good point, a better way to calculate the months would be the DATEDIFF function. DATEDIFF(,,MONTH)
It appears your calculation returns negative "Months Ago" when calculating months from different years
What about for multirow cards?
Good one! but it should be like if all filters are selected then it should show all data instead of current month..and if none is selected then current month data..?
All and none are the same thing here! It’s unfortunate but I haven’t found a solution that can distinguish between all/nine selected since the default slicer functionality dictates that all values are selected when none are selected.
ohk.. thanks for the clarification.
Indeed a cool trick !!!
Thanks Bibin!
Thanks. Great video tutorial.
+Erol Yucel thanks for watching!
Hello, is there a similar workaround to default a slicer based on user log in? The RLS settings work fine, but when the dashboard is accessed the slicers are not automatically selected. What I was hoping to do is:
If user 1 logs in, this user should automatically see his or her own data and the slicer is automatically selected. But based on user 1’s role, he/she should still be able to see other options in the slicers.
What I am having issue now is when user 1 logs in, this user can see the options relevant to him/her but the slicer is not automatically selected by default so the visuals still show blank.
Appreciate your advice. Thanks in advance!
Same here. How to select a a location slicer value from table of user and location as the default slicer? And the kicker, direct query on D365 Dataverse. Why is this so hard? :)
Life saver....thanks!
Thanks for watching, Bro Slayer!
how to count how many itmes selected in slicer in power Bi ?
Can you just put daterange in the page filter and tick select all for a card?
Hi Tedro, not sure exactly what you mean. Can you explain?
Hi I am unable add date range column using mention function can you please help me
Reply please
The method of getting N months back calculation won’t work even when you are in January but want to include November and December.
Hey Anatoli, you're right and I've recently started changing how I do this. I use the following formula: DATEDIFF(Table1[Date],DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())),MONTH)
@@BIElite Thanks.
I used a similar logic to get the selected date first with (this will return the last day of the month from the slicer):
SelectedDate = EOMONTH(DATEVALUE([SelectedSummarisedYear] & "-" & [SelectedSummarisedMonth] & "-01"), 0).
Then
SelectedDateMinusN_Mths = EDATE([SelectedDate], -[Parameter Value]) will return the number of months back in the "what if parameter".
@@Anatoli8888 Nice! I like that
Hi! What if I wanted to set 2 slicers as a default? Let's say Región and Currency. Is it posible to add a "&&" to the SELECTED VALUE filter?
Good question Florencia... you could definitely throw in a && but I think you'd also have to write some more code to determine if this second slicer has a selection or not. Are you going to be showing this second slicer on the page?
I have a similar question. I have 1 slicer, but about 6 different "metrics" that I am displaying in 6 different cards. This means, I need a measure for each of them, since they all use different data.
Is there a better approach for this?
Thanks!
I get the result as Blank, am I missing something, please help
The previous year months also shows the same months ago value as of the current year. Hence not useful to my data as my data has values for 3 years.
Use YEAR(Date) = ... & MONTH(Date) = ..., or the AND function for both year and month.
How can we do this if we have String filters, Thanks in advance
+sripriyabs sorry I don't follow. What are you trying to accomplish?
Actually I have a slicer as a Status which has Active, Inactive and closed. I want to set the default value as active. I understand your approach works good for Date fields but its not working for String field. Hope this is clear
@@sripriyabs yes, I'm wondering the same. I have a map on which I'd like the default country displayed (i.e., no filters selected) to be USA unless the COUNTRY filter is anything other than USA.
Nice!
Thanks for watching!
Hey Thanks Bro, Filtered google to get here and this is close, so can I ask or any expert here some questions. I have a scenario 1. Status that I am set for slicer that has huge list. I cannot use this as this requires aggregate function to be given as must, but I cannot do aggregation and it is cribbing
FTODefaultTest =
If (
COUNTROWS(DISTINCT(ALLSELECTED(_Work[Status]))) <
COUNTROWS(DISTINCT(ALL(_Work[Status]))),
MIN(_Work[Status]), /* I cannot have MIN on values returned by system to select, I am not calling a measure. */
If (
SELECTEDVALUE(_Work[Status])= " Scheduled",
MIN(_Work[Status]),
BLANK()
)
- I get blank, how do we fix this,
2. I want to set multiple options as default
3. Similarly i need to set 3 FY years from current year as default Any help?
where are you at bro
👍👍👍
Lol thanks giavo
Inspiring video, thanks .. I found it when i was looking for a solution for my case, which I have an event calendar table and want to show one event (let's say event A) with each other single events, so it is more like event A should always be in the calendar with the other event(s) which is (are) selected by the user. I appreciate any suggestion/help. Thanks :)
Ah that's a super interesting scenario... I think you will be able to do this using a very similar method to this video. I can't think of the exact solution right now, but if you have a sample PBIX that you could send me, I'd love to take a crack at it. Email: powerbielite@gmail.com