Filter and Date hierarchy

[Uhrond]

Hello,

I have a filter problem on the dates, I can't understand how it works:

I have only one table containing dates as well as a "year" (annee) column corresponding to the year of each date.

If I add a "year" filter and an array containing the dates, the array is not filtered when I select a year in the filter :

It works if I display the dates in the table without the hierarchy.

But I don't understand why it doesn't work with date hierarchy enabled.

Thank you for your explanation

[Anonymous · Accepted solution]

Hi @Uhrond ,

It seems that you enable Auto date/time option. When the option is enabled, Power BI Desktop creates a hidden auto date/time table for each date column. Auto date/time tables are permanently hidden, even from modelers. They cannot be seen in the Fields pane or the Model view diagram, and its rows cannot be seen in Data view. Power BI Desktop also creates a relationship between the auto date/time table's Date column and the model date column. The auto date/time table contains full calendar years encompassing all date values stored in the model date column. For example, if the earliest value in a date column is March 20, 2016 and the latest value is October 23, 2019, the table will contain 1,461 rows. It represents one row for each date in the four calendar years 2016 to 2019. When Power BI refreshes the model, each auto date/time table is also refreshed. This way, the model always contains dates that encompass the date column values. You can find the following documentation for full details.

Apply auto date/time in Power BI Desktop

When you want to Date Hierarchy, you can also add the slicer field onto the table visual. Then it will be filtered correctly just as below screenshot...

Or you can apply the Year field from Date hiererchy as slicer option:

Best Regards