Forum Discussion
Helper IISingle date slicer to filter multiple date tables
Shouldn't your slicer be based on the dates in a Date Table where the Date field is related to all of the relevant date fields in your other tables?
Community ChampionAs edhans suggested, you should have a calendar table (i.e. a dimension table for dates) with relationships with your fact tables, and set up your filters against dimension tables rather than fact tables.
Years ago people used to import big pre-created calendar tables (e.g. from a relational database), but if you google around you'll now find plenty of solutions using either M (my preference) or DAX. Here's something to get you started, there's plenty more related content on the web:
- edhans
jscottNRG wrote:Thank you otravers, I'll give that a look!
Make sure in the new Power BI Desktop (Since May 2018?) to mark your date table as a date table. When that article was originally written Power BI would figure it out and do it for you. But it is good practice to:
- Have a date table in every model that has dates in it.
- Mark the table as a date table. Select the table in Table view, then go the Modeling tab, then Calendars, tehn Mark as Date table, then select the Date column.
If it gives you a warning about "built in tables" are removed, that is find. I prefer to control the date table vs letting PBID do whatever it thinks should be done in the background.