Date coloum not responding to filter. Nothing but date.

[PhilSmith]

I have a date table, which comes from a spreadsheet, and two filters, year, and month.  When I put a particular column into a table, it does not respond to the filter.  Other column works just fine.   The table in yellow should only display 2019, right? Trying to figure out why and how to recognize it in the future.  Thanx.  datetest.pbix

[Ritaf1983 · Accepted solution]

Hi @PhilSmith 

I looked at the attached file and noticed that the 'year' is not actually a standalone year column but the result of an automatic hierarchy generated by the auto date/time feature.

The automatic date hierarchies can be quite misleading. Not only do they take up more memory and slow down your model, but they also limit flexibility in how you manage and filter your dates. That's why I always recommend creating a dedicated date table with properly formatted columns (Year, Month, etc.) instead of relying on the built-in auto hierarchies. This approach ensures better control over your filters and data presentation, and you can avoid unexpected behavior like the one you're seeing here with the 2019 filter.
To switch off the automatic hierarchies fo to the options and switch off "auto date time"globally and in the current file

2. Mark the table as a date table :

Now if you need hierarchies you can create them manually :

And add to it the wanted columns :

After finishing you can add created hierarchies to the visual and the slicers will work as expected :

The updated pbix is attached.
I also recommend the linked video guides :
https://www.youtube.com/watch?v=xu3uDEHtCrg

https://www.youtube.com/watch?v=x6vXVJZ_eTY

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[PhilSmith]

Thank you!  A very thorough answer.  I was missing a that step in a couple of my files.

[Ritaf1983]

Happy to help😊

[lbendlin]

you are using Auto date/time.  While acceptable in some limited scenarios it is generally not recommended as it has a lot of bad side effects.  Use your own hierarchies from your Calendar table.