Year Filter on Filters Pane Requiring Single Selection Disables Single Selection If User Deselects

[AndrewLGoldman]

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!

[Anonymous]

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.

[AndrewLGoldman]

Thanks @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 format

Any ideas?

Thanks again for the really good idea above though, that's great!

[Anonymous · Accepted solution]

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.

[AndrewLGoldman]

Apologies for the delay in response!  I can see how that solution would definitely work, but I actually had a standard date column I set custom formatting to be in the style of "mm yyyy" and enabling 1 slicer instead of 2 without drill up/downable functionality

[MFelix]

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.