Forum Discussion
Single date slicer across multiple queries
I would add a New Table and use this formula to generate a calendar of all required dates (with the year ending in December):
Dates = CALENDARAUTO(12)
Then I would use Manage Relationships to add relationships between that new Dates table and your existing tables.
Finally I would add a slicer, using the Dates.Date column.
I would add a New Table and use this formula to generate a calendar of all required dates (with the year ending in December):
Dates = CALENDARAUTO(12)
Then I would use Manage Relationships to add relationships between that new Dates table and your existing tables.
Finally I would add a slicer, using the Dates.Date column.
Thank you, Mike!
Taking your advice I created a calendar table. Instead of creating a DAX-based calendar, I chose to create one in Power Query to give me more control. I used the Power Query code here.
I then cleaned up my table relationships. I found that slicing the date in the Calendar table wouldn't work as predicted if I had spurious inactive relationships floating about. I cleaned up my table relationships to be as simple as possible and voila! I really snazzy single slicer now drives the entire report!
Woohoo!
Sounds good - thanks for the whoohoo ...
More effort in the query and modeling end usually pays off down the track.
I'm a huge PQ fan too - my example was just the quickest way to get you going. That script is a great starting point.