Forum Discussion
Advocate IYear Filter on Filters Pane Requiring Single Selection Disables Single Selection If User Deselects
Hey Power BI Community,
Not sure if anyone else ran into this or has any ideas, but I would be so grateful if anyone had any ideas! Right now on one of my Power BI Desktop reports I have a filter that enables you to filter by year as follows on the filter pane (note: not a slicer, this is intentional, unfortunately a slicer is not an option for me at this time):
and this works amazingly well and truly only allows a user to select 1 year at a time! But unfortunately if a user selects the same year again/deselects the same year (de-selecting 2020 in the example above), the data set then filters for all years (as expected I guess!) But that's unfortunately the rub for me - if a user has no year selected, I would want no data to show instead of all of the data such that a user can either show no data or only 1 year's worth of data, or even to not be able to unselect a year and always be forced to pick 1 year from the options -- does anyone know if anything like this might be possible or if there might be any work arounds? Something perhaps similar to a radio button list instead of a picklist for the filter's pane! Again, unfortunately a visual slicer is not a workable solution for me at this time, but I appreciate any advice!
Thank you!
Hi AndrewLGoldman ,
Are you mean you want years can't be multi-selected but months can be?
Measure = IF(CALCULATE(DISTINCTCOUNT('Table'[yearmonth].[Year]),ALLSELECTED('Table'))>1,BLANK(),1)Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- MFelix
Super User
Hi AndrewLGoldman ,
A possible workaround and depeding on your visualizations is on your measures make a similar sintax to this one:
Measure = IF(DISTINCTCOUNT(Table{Year]) > 1 , BLANK(); [Measure]))This will blank out your data, but again depends on how you have your data setup.
Hi AndrewLGoldman ,
Thanks MFelix to give the direction.
I modified the formula and used ALLSELECTED() function.
Here's the formula i used and the result as below.
Measure = IF(CALCULATE(DISTINCTCOUNT('Table'[year]),ALLSELECTED('Table'))>1,BLANK(),1)What we need to do is adding this measure to visual level filter.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AndrewLGoldmanThanks Anonymous MFelix - those are great ideas for blank-ing out the data in the data tables! Works like a charm! Nice save for some of the data tables
Would either of you maybe have any ideas for charts as well? For example, a simple bar chart with MM-YYYY on the axis and some value as the value -- when a user deselects a year and let's say there's 5 years worth of data (e.g. 2016 - 2020), the bar chart expands horizontally exponentially with 60 columns when I rather just show 1 year's worth of months or even better no data or axis at all as opposed to every month between the first year and last year in the data set
I guess I could show just the month name but I find it's much more visually appealing to keep it in the MM-YYYY formatAny ideas?
Thanks again for the really good idea above though, that's great!
Hi AndrewLGoldman ,
Are you mean you want years can't be multi-selected but months can be?
Measure = IF(CALCULATE(DISTINCTCOUNT('Table'[yearmonth].[Year]),ALLSELECTED('Table'))>1,BLANK(),1)Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
