Forum Discussion
Creating date slicer removes data
Problem - when creating a "Date" slicer for information in my visual, I lose data from a table I have set up.
Example:
Setting up a slicer based on the Date table, you can see there is a range that is greater than the listed order dates.
When the slicer is "used", but without changing any dates, data is now lost. It caps dates at 8/21/2019.
Sources - Date table (import), Order data table (direct query)
Date column from Date table is set up as a Date data type, and set to Short Date format.
Order Date column from Order data table is set up Date data type, and set to Short Date format.
Relationship - From Date table, Date Column to Order data table, Order Date column, 1 to Many cardinality, active relationship, single direction.
Table relationship link
Table relationship link details
I'm honestly at a loss of what is going on, and at a loss based on a lot of the other threads I've been looking through.
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.
15 Replies
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.
Hi raschdieek , Thank you for reaching out to the Microsoft Community Forum.
This isn’t a slicer or Date table problem. It’s a side effect of using an Import Date table with a DirectQuery orders table. When the slicer is first placed, Power BI may show data without fully pushing the filter to the source. As soon as you interact with the slicer, even without changing the range, Power BI re-runs the DirectQuery and pushes the Date filter through the relationship. At that point, the source only returns data up to 8/21/2019, so anything after that disappears. Power BI is just showing what the source query returns.
The clue is the fixed cutoff date. That means the limit already exists on the DirectQuery side, for example in a view, security filter, parameter or a DATE vs DATETIME mismatch. Check the source query and use Performance Analyzer to inspect the SQL after touching the slicer. Once the source returns the full date range under filtering, the issue will go away.
Thanks for the tip on the Performance Analyzer.
I'll need to work on my SQL, but when interacting with the splicer, it shows the following:
EVALUATE ROW( "MinDate2", CALCULATE(MIN('Date'[Date])), "MaxDate2", CALCULATE(MAX('Date'[Date])) )Data listed:
[MinDate2] [MaxDate2] Mon, 01 Jan 1900 12:00:00 AM Fri, 31 Dec 2049 12:00:00 AM Return:
DEFINE VAR __DS0FilterTable = FILTER( KEEPFILTERS(VALUES('Date'[Date])), 'Date'[Date] < DATE(2050, 1, 1) ) VAR __DS0Core = CALCULATETABLE( DISTINCT('oe_hdr'[order_date]), KEEPFILTERS(__DS0FilterTable) ) VAR __DS0PrimaryWindowed = TOPN(501, __DS0Core, 'oe_hdr'[order_date], 0) EVALUATE __DS0PrimaryWindowed ORDER BY 'oe_hdr'[order_date] DESCThe return data (partial list below) shows up as all date items with the 12:00:00 AM time stamp associated with each date:
"Wed, 21 Aug 2019 00:00:00" "Tue, 20 Aug 2019 00:00:00" "Mon, 19 Aug 2019 00:00:00" "Fri, 16 Aug 2019 00:00:00" "Wed, 14 Aug 2019 00:00:00" It looks like the issue is that the date ranges passed to the filter are taking a time stamp into consideration.
A time stamp of 12:00:00 AM is added to each date, even though the Date data in the Date Table is the Date data type, and in the Short Date format.
The Order Date data in the Order table is also set to Date data type and Short Date format.
For the Order table, I can create a new column by duplicating the date column
But, when I try to format it to "Short Date" as follows:
OrderDateNoTime = FORMAT([order_date], "Short Date")I get the following error:
"Function 'FORMAT' is not allowed as part of calculated column DAX expressions on DirectQuery models."
I'm altering the table to be Import vs. DirectQuery to see if I can better manage.
Hi raschdieek
Do you need those future dates? Have you tried removing overly future dates from your dates table? Say only up to 2027.
Thanks for the reply. No, I do not need the dates that far out, but it is not the issue.
The issue looks to be that the underlying data that gets filtered still has a time stamp associated with it, even though in PowerBI I set the column data to Date data type and Short Date format.
Visually I do not see the time stamps, but the query to our database still contains a time stamp.
When you use the date field in your date table, it's a normal reaction for Power BI.
You can use between mode to specify dates from beginning, or a start date from 2019.
see another post here :
https://community.fabric.microsoft.com/t5/Desktop/Default-Date-for-date-slicer/m-p/4892430
Hi raschdieek , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.
