Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
leolapa_br
Resolver I
Resolver I

Hide future year(s) from slicer visual

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

 

Slicer.png

 

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!

1 ACCEPTED SOLUTION

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.

 

Filter.png

 

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

 

Ano Slicer Table.png

 

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.

View solution in original post

4 REPLIES 4
DataNinja777
Super User
Super User

Hi @leolapa_br 

 

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

Sakiko_1-1694121927300.png

 

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

 

 

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.

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?  

Sakiko_0-1694123701733.png

 

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.

 

Filter.png

 

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

 

Ano Slicer Table.png

 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.