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 , 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] DESC
The 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.
It turns out that changing the table from DirectQuery to Import still didn't do what I needed it to do.
I deleted the table from the model and reloaded it, this time transforming the order Date column to short date format.
This transform was able to get rid of the underlying time stamp format for me.
Hi raschdieek , Thanks for the update and providing the details. We are really happy to know that your issue is resolved now. If you have any other queries, please feel free to create a new post, we are always happy to help.