Constrain filters to facts

paivers

Board Regular
Joined
Sep 4, 2009
Messages
64
I would like to constrain a filter or slicer to values represented in the related facts. For example, if I only have sales for the current year, I would only want the date slicer to show this years date. As it is, the drop down shows all dates from 1900 to 2100. Is there a way to constrain that?
- Pete
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
If you are using 2013, this is pretty easy.

Right click on the year slicer, go to Slicer Settings, and check the box for "Hide items with no data"

In 2010, it is not as easy. You need to make sure your date table does not contain dates outside the range of dates in your fact table. There are several blogs out there about dynamically creating the date table. Depending on your data sources and programming abilities this can either be quite challenging or relatively straightforward.

Or if your data doesn't refresh often, you might just manually create the date table from the proper range.
 
Upvote 0
Thanks Mike. That works for slicers. If the field is used as a pivot filter, or on the row with a label filter, there doesn't seem to be an option to do that. These are the more common scenarios for dates as you can't really get 3 years of days in a slicer. Ideally I want to do it in the model so that end users don't need to bother with it. The data does change frequently with daily transactions. We might have to filter the data dimension read based on the min/max transaction dates.
 
Upvote 0
So, first of all... don't put 1900-2100 in your calendar, because your performance will be terrible.

Ignoring that... what you are saying should "just work". The slicer will respond to whatever you drop into the values portion of the pivot tables the slicer is hooked to.
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top