Forum Discussion
Creating date slicer removes data
v-hashadapu Thanks for your help.
I would consider this resolved. I can transform the data that is pulled into the model from DirectQuery as well. This is suitable for my needs.
Hi raschdieek
The issue occurs because two tables with different storage modes are connected and interacting in the report the Date table is in Import mode while the Fact table (Orders) is in DirectQuery mode. When you use the slicer, Power BI sends a filter request from the Date table (stored in Power BI memory) to the Fact table (stored in SQL Server). If the filter range doesn't match properly between these two different environments, incorrect data is returned, causing data to disappear.
However, when you change the Date table to Dual storage mode, it acts as DirectQuery when connected to the Fact table at the visual level. This means when you click the slicer, the date range maps directly from the Fact table within SQL Server itself, and both tables are joined in the same engine. This ensures the filter always matches correctly and returns the complete data.
Possible Fix: Change Date table storage mode from Import to Dual.
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
The Date table that was generated is not coming from our server, so I cannot update the Date table to "Dual" to potentially pull from the server as well.
I will take a look at the Order table and switch it to dual to see if that will work.