The date dimension table, a basic ingredient in any BI dataset, enables us to enrich a date with many attributes such as day of week, workday/weekend/holiday, financial year, and the list goes on, depending on your needs (Here’s an previous article on its importance).
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.
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. So, a typical case might have dates from 1990 until, say, 2040. That’s a pretty wide range of course, and the annoying behaviour is that, by default, the date range shown to the users in filters or slicers, includes the entire range, 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 will ensure that when you show data from both tables, the actual dates in your fact table will be used to filter 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 in the date dimension, the fact table has nothing to do with this visualization, so 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.
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, if you choose a “sales amount” filter, which is always positive, set it to 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.