The Date Dimension

The date dimension table, a basic ingredient in any BI dataset, enables us to enrich a date with many attributes. A few examples are: day of week, workday/weekend/holiday, financial year, and the list goes on, depending on your needs. Here’s an previous article on the importance of the date dimension.

This is why in 99% of my Power BI datasets, there is a date dimension connected to the fact tables. Whenever I need to filter by date or present a filter/slicer to my users, I use the date field in the date dimension, instead of a date field in a fact table. I’m sure this practice isn’t new to anyone who’s been building BI systems.

The Power BI Date Slicer Problem

However, there’s one annoying Power BI behaviour which I’ve been wrestling with from time to time, and I’d like to share a reasonable solution. Since I’m reusing my date dimension table in many datasets, I make sure to keep a wide date range in there, and not just the range that is relevant for a specific dataset. As a result, a typical case might have dates from 1990 until, say, 2040. That’s a pretty wide range of course! The annoying behaviour is that, by default, the date range shown to the users in filters or slicers, includes the entire range. This happens even if the fact data holds information for 2015-2017 only. Especially with date sliders, choosing from a wide range can be tiresome:

Fear not, here is a solution, and it has two steps

First, you need to define the cross filter direction to “both” in the relationship between your fact table and the date dimension. This ensures that when you show data from both tables, the actual dates in your fact table act as filters the list of dates in the date dimension.

Second, when presenting a date filter or slicer, you need to make sure that the fact table is somehow involved in the query. By default, if you just display a slicer, showing a field from the date dimension, the fact table has nothing to do with this visualization. In such case there’s no filtering coming from the fact table’s direction, meaning the actual date range in the fact table does not influence the date range in the date dimension.

Wait, One More Thing To Do…

We need some sort of a trick to make the relationship active in the query even though the fact table is not involved. The way to do it is to define a page level filter on some random field in the fact table, and actively set it to include all values. For example, consider a “sales amount” filter, which is always positive. Set a filter on it to be greater than zero. The mere existence of the filter on the page level, will “activate” the relationship and change your date slider to one that makes more sense in the context of a specific dataset:

If you prefer, you can use this “dummy” filter on the report level – this can help in case you have date slicers or filters on more than one page.

Hopefully this tip helps you. If you like it, be sure to subscribe for updates.

If you enjoyed the content of this article, please consider sharing it with your network on LinkedIn. As an independent consultant, this helps my business. Thank you!