Dynamically Enable and Disable Slicers in Power BI

[@SQLDevDBA]

This is so hilariously simple, but so useful. I have so many workarounds I've used in the past (like bookmarks) to accomplish this, but never thought about it this way. Love the slicer header trick as well! Thanks so much Bas!

[@ereg31415]

Really great idea to disable the slicer! You can also change color via rules, then you don't need to create a measure and find the specific hex code

[@HowtoPowerBI]

thx!

[@alexgry4763]

@@HowtoPowerBI Still sad that Microsoft makes us abuse the tool instead of introducing a conditional visibility.

[@MrSparkefrostie]

We are using this and have a Custom option in the date period selector, this will make things a lot neater. The one thing we have not gotten right is resetting the date slider when flipping between date periods.nneed to hit the eraser and then the period, given the text bit I think it will still affect this, likely this will help others who use this, unless this works without that

[@kingslayer1156]

Great video. Do you have a video on how to create L12M, 6M period measures. A link would be much appreciated

[@alexgry4763]

Measure = VAR SelectedMode = SELECTEDVALUE( Period ) VAR MonthLag = SWITCH( TRUE(), SelectedMode = "Last 3 Month" , -3, SelectedMode = "Last 6 Month" , -6, SelectedMode = "Last 12 Month" , -12, 0) RETURN CALCULATE( [Some Measure], DATESBETWEEN('Date'[Date], TODAY() + MonthLag*30, TODAY()) )

[@jabiermartines]

Thank you. Very useful. I am a beginner and although I have already created several reports I have always wondered how exactly to do this Very well explained Cheers from Barcelona

[@rafaybahelmi]

Hi Bas / @HowtoPowerBI Thanks for the detailed video. I have implemented similar solutions in the past. However, with my solution, the slicers conflicted and returned incorrect results when the Period slicer is disabled and the date range slicer is selected (for example, the last 3 months from January to March). After this, when L12M is selected, it retains filters from both the Date Range slicer and the Period slicer, showing only the last 3 months. How does the behavior in your implementation differ? Could you please clarify? Thanks a lot for this and all other videos. Much appreciated.

[@poojadattachoudhury]

great video i have implemented same kind of logic in my recent development .. but changing the colour and text of visual is the cherry on cake .. will be implementing the same as well.. thank you

[@PaulEBrownbill]

Thanks for this video. Your explanation makes it seem so simple, and will be very useful. Paul

[@CS-cl3ij]

This is really smart! I thought it was going to solve my issue, but it's just off. I have 2 slicers that I'm experiencing this problem with but they're both referencing the same field. The reason is they wanted a quick date slicer to choose custom number of weeks and such but also want to be able to easily toggle custom date ranges(with the between type of slicer), so this solution doesn't work. From the comments, I may try an old school bookmark though

[@LeonidKostin]

is there a way for you to duplicate this field but name differently? I think you can reuse the described above approach but you just need to separate the fields by creating the second one as a date table

[@AbhinavPhukan-gd1di]

Thanks mate. There is some issue with slicer of style 'Between'. We cannot disable and enable based on selected values of another slicer.

[@MrPloinky]

Hi Bas, good one again. Many thanks - I succesfully implemented it into my reports. One isue is that when a selection is made in the second slicer when active, the selection in the second slicer remains in place when moving to a disabling category in slicer 1. I have tried to find a way out of that but so far unsuccesfull. Any sugestion for solutions for this from your end?

[@usman_oz]

Nice techniques for UX. However, dont you think if we have multiple slicers with such functionality & also several visuals with similar UX techniques which invlove creation of custom measure/columns / buttons / bookmarks / interactions, etc would make the report more complex to manage when someone would want to make changes to report after few months or if report is handed over to a different developer to enhance or maintain ? Thanks

[@HowtoPowerBI]

Sure, you can also go for a mediocre report that everyone understands how to build all features .. depends what is more important to you

[@excel-solutions4564]

Great video! Just one thing, if we have a selection in promotion slicer and than we select cataloge store, it will not deselect the selection in promotion slicer, right? How to handle it?

[@henriquemerolafaria2569]

Simple and powerfull. Amazing, I'm using right now !!!

[@marekbona4987]

Hello sir, thank you for this video and idea ! Works perfect with these two types of slicers, but i m working on project right now where i use dropdown slicers and it actaully makes a difference, but im still able to "open" dropdown slicer but i see nothing there using your method, which works fine but is it somehow possible to disable dropdown slicer the same way how you did that with date slicer ? My last question is, is this possible with buttons aswell ? tu use measures to disable and enable buttons in powerBI ? Thanks for your contet and have nice day !

[@juanignacioXI]

Hi Bas glad to watch ur awesome new videos again, just a question please. Inside your report you have a measure used as a filter that is the "Customer Period Filter", inside there you have this VAR: VAR _MinDate = EOMONTH( _MaxDate , _MoveMonths ) + 1 Can you please explain me what the +1 refers to? Thanks in advance

[@mariuszkrzemien6576]

Unfortunately it doesn't work for me, slicer with dates is still visibile, evertyhing works when slicer is for exmaple dropdown list but when I switch to slicer between it doesn't work, helpppppppppppp

[@stefankleinhans4895]

Can you do this with the same date table. I need to show or hide date (Relative) and date (Between) and clear selection after toggle.

[@The-Developer-BI]

Friendly Suggestion No thumbnail ✋ Maybe You forgot to choose ... Make it fix✅❤️

[@HowtoPowerBI]

oops

[@AyanKhan-d6f8v]

Two Power BI Legend in one Frame❤😍

[@worldofdata]

simple yet very effective use case, great video :)

[@roverre61]

Bravoooooo Bas...!!! Always great ideas and inspiration. Many thanks ❤

[@HowtoPowerBI]

Thank you! Cheers!

[@harshilmarwadi1958]

🎉 That's exactly what i needed in my report

[@johnbaisley2799]

I tried this method and it works great on the desktop but when I publish to the service the date slicer still allows the user to click on the date to select a date, it just doesn’t actually filter anything. Was very confusing for the users

[@AnishShrestha-u3o]

How do I reset the date slicer before disabling?

[@gettingby987]

Disabling the slicer does not clear the previously selected values in it. So if you were to filter on date range, and then select a period, the filtered date range values are still in effect, and therefore the selected period could falsely represent the amounts. Is there a way to make the "disabled" slicer go back to default or be cleared, when the other slicer it depends on is being filtered?

[@gettingby987]

Not making it with bookmarks since that kind of defeats the purpose

[@rahulmaurya9444]

For date slicer I am using bookmark to show one slicer at a time. But this looks interesting.

[@alexgry4763]

That is one way to go. However, bookmarks scale multiplicative. Let's assume that you have 4 bookmarks. If you add some another factor, which needs 2 more Bookmarks for different states (like 2 different chart types or showing/hiding side panel for ex), you will end up creating 2x4 = 8 bookmarks. And that gets increasingly out of control very fast.

[@nizar_arena]

As always phenomenal, thanks bas !!

[@cmagnos]

Great videos, I'll implement this technique soon.

[@MarcoAntonio-yj4br]

Amazing as always! Thank you!

[@paulgeorges2267]

Once again a very informative and clear video! 👌💪👏 Correct me if I'm wrong but on Excel slicers it's a native feature to hide value with no data. Weird it's not native on PowerBI ?

[@dmitrypopok]

Unfortunately, this does not seem to work with Relative Date and Relative Time type of slicers

[@fanlovexcel]

Nice trick again! Very insightful 😊

[@QuantumIdeas]

Truly useful. Bookmarked!

[@Fannie2uN2010]

keep going Bas, we love you

[@Harika2775]

If the slicer changes the date and back to click L6M the date is not disable how to do

[@borteyhumphrey]

I have a similar setup but with a Year slicer and a Date slicer (range/between). Implemented this logic but it does not work for some reason. Would appreciate your help.

[@HowtoPowerBI]

Maybe check the download file and see what you did differently

[@Art_Van_Delay]

I really appreciate your videos. Could I make 1 small suggestion though? Could you please put the entire Power BI application in your background and make the video of yourself a little smaller in the corner? I don't know if it's just me but it's so hard to follow which window you're in or which part of Power BI Desktop you're in when all we see is the little window of the part you're working on. We appreciate seeing your face but maybe it'd be okay if it's a little smaller in the corner so we could see more of what's happening. Most of the time, I end up just going to another channel because I don't understand where you are in the application. Thanks again.

[@HowtoPowerBI]

thanks for the feedback. I prefer the editing style as is, so probably wont change any time soon

[@anupamjha827]

Can we use selected value for disabling time range

[@mustafabiviji00711]

Bas, thanks for the trick! Is the line chart continuous or categorical? I can see it is a time series on X-axis which generally is plotted on a continuous line chart. But you have breaks in the line chart which is not possible in Power BI (unlike Tableau) ... This is required especially for sensor data where the sensor value is missing for certain date-time values.

[@nicolajpetersen4673]

Can it be true there is no file to download? i cant find it on the page after clicking on the link. I did try type in my information. I couldnt sign in either, it said "registration has been disabled"

[@down_with_data]

As always amazing

[@RadAlzyoud]

Brilliant. Thx for sharing.

[@DineshKumar-rf9eh]

That’s really useful. What will happen if two or more values selected in the Period slicer? And can we make it dynamic for the promotion slicer if it does not exist for the selected Store?

[@HowtoPowerBI]

you would need to set that slicer to single select only

[@HowtoPowerBI]

it is dynamic

[@MrSparkefrostie]

You could also add your periods and a "custom" option with forced selection, only downside here is the user needs to click the eraser on the slider when switching to a fixed period

[@AlphaPrime808]

Hi, thank you very much for the solution......now a question, as you left your date filter with those bars at the bottom, mine only shows those circles haha.......I'm referring to the date range dates.

[@yuliiakamkova7022]

try to turn off responsivnes of the slicer in the advance options of Format :)

[@jainilvora7167]

Is there any way to disable 3rd value selection in a same slicer because i want to restrict user to select only two state at a time if user try to select third value from same slicer slicer gets disable

[@arturpiwowarski7775]

How about single selection? :)

[@ashishmohan4707]

Amazing great New learning in Power BI

[@HowtoPowerBI]

Glad you think so!

[@OOA_BI]

Thank you sir 😊

[@TheAtulsachan1234]

Disabling a slicer when you know that from calalog stores we do not sell via promotion. How about those scenarios when we are even aware if sell anything via promotion? I am looking at a dynamic hiding whenever there is no selection available on the second slicer.

[@sanchowitfurrows1561]

try to use single column tables instead of slicers

[@ankurgupta4325]

You are awesome

[@8g8819]

You are a beast!

[@leonelalegria7787]

TOP! Thanks!

[@HowtoPowerBI]

THANKS! 😁

[@azwarmzafar]

Great tips many thanks

[@HowtoPowerBI]

😁thx

[@iiiiii-w8h]

It's ridiculous the number of "hacks" you need to do in order for power bi to work properly

[@nevermore17011990]

beyond godlike

[@HowtoPowerBI]

😁 thx!

[@avishekghosh8134]

This solution was available all along. The problem with this solution is that if any of the options from the slicer to be hidden is either pre-selected or selected during the usage that option will continue to show even when it is not supposed to show. It only works as long as no option is selected from the slicer to be hidden. The moment one option gets selected, the functionality is gone for a toss. Not a realistic solution.

[@Koikby79]

I can’t find this pbix file to download? Is that an error?

[@niltonsantos479]

Perfect

[@MrHaddad]

Morning dear, how I can showing the group "matrics" on the right panel like I saw in your vedio?

[@benndii]

Just create a new table in the modeling view with no content or one column. Rename that and done.

[@HowtoPowerBI]

kzbin.info/www/bejne/mXjRiIOrlpKWo9E

[@AhmedCherif-mt5lb]

Thank's 👏☺️

[@HowtoPowerBI]

You're welcome 😊

[@IvanKashperuk]

What about hiding it instead of disabling?

[@LucaGanugi]

A very basic feature that's still missing unfortunately

[@HowtoPowerBI]

that would be nice

[@mcnater]

Something Qlikview had 13 years ago that PBI for some reason can’t do.

[@JamesBatchelorGoogleProfile]

Agreed.. Hiding or changing layer value to bring forward or back. But this is good progress in capabilities.

[@MagdyTawfikA]

Great illustration, one lingering question: can we filter some of a slicer items (not the whole slicer) based on selection of another slicer? By the way, I found the answer to my question at one of your videos: SMARTER Slicers = BETTER User Experience | Show Only What's Relevant in Your Slicers in Power BI kzbin.info/www/bejne/n5bchKGsgpubjM0 Thank you so much!!

[@mariamolchanova3831]

why do you always look like you would benefit from more sleep? please address your health, we do rely on you and would like to see you around.

[@sum_andres31]

That's a really distasteful comment to make

[@theoldvocals266]

Absolute rubbish comment to make.

[@edwinarchico439]

Awesome, got to use this in my dashboard

[@HowtoPowerBI]

💻🤓😁 hope you got it up and running