topic Hide future year(s) from slicer visual in DAX Commands and Tips

Hide future year(s) from slicer visual

leolapa_br — Thu, 07 Sep 2023 21:45:44 GMT

I placed the following "Year" ("Ano") slicer on a report page by adding the year column from the date table 'dData[Ano]':

It however shows a year in the future, since 'dDatas' goes all the way through the end of 2024 (it needs to continue this way). But I need this slicer to show only the current year plus all available past years.

How can I set a dynamic threshold that could make the slicer hide this and any other future years that might come up?

I tried playing with the filter pane but couldn't find anything that could get that done.

One of the filter pane options is to set a top or bottom value, so I created a measure DISTINCTCOUNT( dData[Ano ) to dynamically set this value, but when I place it on the well the "Apply filter" is still greyed out so that's also not working for me.

And I tried creating a measure with GENERATESERIES but the slicer "Field" pane won't even take this measure when I select it.

I get the sense this should be a simple fix, but I ran out of options to the best of my (limited) knowledge and really need that future year (2024 on this case) gone from the slicer. Please help!

Re: Hide future year(s) from slicer visual

DataNinja777 — Thu, 07 Sep 2023 21:30:34 GMT

Hi leolapa_br

This can be done through adding date field in the filters pane and then setting the filters like below:

It appears that your "Year" field is in text format rather than numerial format. Is this correct? Alternatively, you can also untick "2024" in the "Year" field through filters pane.

I attach an example pbix file.

https://1drv.ms/u/s!AlqFfzVTqicpnXnwYGpHFHW9hZaK?e=J0B7Fk

Re: Hide future year(s) from slicer visual

leolapa_br — Thu, 07 Sep 2023 21:44:17 GMT

Hi DataNinja777 and thanks for the reply.

When I was playing with the filters pane I noticed I could manually set limits so the slicer can only show the years up to the current year (2023 as of now), just as you're suggesting.

However, and that's why I highlighted "manually" above, this would only solve the issue at the present moment. Once we get to a new year I would always need to make sure the filter pane is reset to this new upcoming year, and so on, and that unfortunately doesn't work for me.

What I need is a solution that dynamically sets the threshold and therefore needs no manual intervention in the future.

And to answer your question, my "Year" field is in numerical format. Only years are shown on the slicer because I have a column 'dDatas[Ano]' on my dates table that only displays the years.

Re: Hide future year(s) from slicer visual

DataNinja777 — Thu, 07 Sep 2023 21:56:35 GMT

Hi leolapa_br

Certainly. It is better to do it dynamically as you said for easier maintenance. What about setting the filter type as "Relative date" as shown below?

Re: Hide future year(s) from slicer visual

leolapa_br — Thu, 07 Sep 2023 22:48:52 GMT

The funny thing is that for me the filter pane does not provide a "Relative date" option probably because Power BI reads my "Year" (dData[Ano]) column as integers values, not as actual dates.

But the good news is that I reached a solution by creating the calculated table below with GENERATESERIES.

I then connected the above 'Ano Slicer' table to the date table through dData[Ano] via a one-to-many relationship and when I placed it on the slicer's field pane, voilá, I now have my slice with only the years I want!

My mistake before was that I was replicating this code as a measure, but the slicer's field pane apparently only accepts table columns as data arguments.