Forum Discussion

raschdieek's avatar
raschdieek
Frequent Visitor
8 months ago
Solved

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 t...
Need Help
  • raschdieek's avatar
    raschdieek
    7 months ago

    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. 

v-hashadapu's avatar
v-hashadapu
Icon for Community Support rankCommunity Support
8 months ago

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.

raschdieek's avatar
raschdieek
Frequent Visitor
8 months ago

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 AMFri, 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.