Forum Discussion
Cross Filtering calendar table
Thanks for your comment. I found a much simpler solution. When you add a column to the values bucket, only the records with data in that column appears in the filter list.
Thanks,
Anton
CALENDARAUTO() does something similar. It looks at the rest of your model and builds a calendar with every year your data contains. So if your sales are May 2013 thru Sep 2018, CALENDARAUTO() builds a calendar table from 1/1/13 - 12/31/18.
Thanks for the replt dedelman_clng. Due to many reasons I have to use the calendar table from my data warehouse. Calendarauto is not an option for me.
I'll preface by saying I'm not very familiar with the limitations of DirectQuery (which I am assuming this is? You are not actually pulling the calendar table into PowerBI?), so while I'm pretty sure this would work if you had the calendar table locally, no promises in a DirectQuery scenario.
In the model, set up the filter direction between Calendar and your Fact table as "both", and then use the date on the Fact table to populate the slicer.
Again, may not work in your scenario if you have no control over the local PBI model.
I'm using the import mode. And yes, the calendar table is being pulled into PowerBI. Making the filter direction to both doesn't work for me because my sales table has gaps between dates and without a conscious date column I am unable to use time intelligent functions.
Thanks.