Creating date slicer removes data

[raschdieek]

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]

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.

[Royel]

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!

[raschdieek]

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.

[v-hashadapu]

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]

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. 

[raschdieek]

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.

[raschdieek]

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. 

[v-hashadapu]

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.

[raschdieek]

@v-hashadapu I would say that I have found a workaround for my issue.

I am still uncertain as to why my date table, when generated in the short date format, still pushes a query with a time stamp.  What this seems to cause is selecting exact date + time combinations, where I do not care what time of day items were processed. 

I am uncertain if this is a SQL or PowerBI-based issue that I am dealing with. 

With that, I am uncertain if I would consider the core issue resolved, but I can move forward with my project.

[v-hashadapu]

Hi @raschdieek , Thank you for reaching out to the Microsoft Community Forum.

The behaviour you arere seeing is expected because the Orders column is a datetime at the source, not because of the slicer or the Date table. Power BI always evaluates filters using the underlying data type, not the display format. Setting a column to Short Date only changes how it looks, it does not remove the time component or change how queries are generated.

So Power BI is not adding a timestamp or intentionally filtering by exact date time values. The timestamp already exists in the source data and becomes visible when slicer filters are pushed down. Transforming the column to a true Date during ingestion removes that time component, which is why your workaround works and is the correct fix when time is not relevant.

[AntoineW]

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

[danextian]

Hi @raschdieek 

Do you need those future dates? Have you tried removing overly future dates from your dates table? Say only up to 2027.  

[raschdieek]

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.