Date slicer is not selecting correct first and last date (between) when using direct query

[Anonymous]

I have a report which is connecting to the data source via direct query. 

I have a column of data (sales date), formatted as a date, that I am using as an on page slicer.

The visual loads fine, and filters the data OK, BUT it does not default to the correct range (min (first) and max (last) of returned values). 

As far as I can see, the field is corrrectly formatted as a date and the value technically works as a filter in the filter pane, but the slicer date range does not represent the data range itself. 

I also created two test measure (using FIRSTDATE and LASTDATE) and they are picking up the range fine, so I'm not sure why the slicer is unable to do it. 

Any thoughts on how to address this would be appreciated as it's a frustrating additional step that users are needing to take on the report currently. 

First and Last Date Measure (Testing)The default date range for the slicerSome sample values in the queryThe column in PQE.

[v-alq-msft]

Hi, @Anonymous 

I failed to reproduce your scenario. It worked properly on my side. I wonder if there are some slicers or filters applied on the slicer visual.

Without filters:

With filters:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @v-alq-msft  but there are no filters in play at all. Here is a brand new page with just two slicers on it using the exact same data as  the input:

Default Slicer: 

Timeline 2.1.1:

You can see that timeline 2.1.1 shows the correct range whilst the default slicer does not. Any other ideas? 

[Anonymous · Accepted solution]

I've managed to solve the issue. When query folding was active (true) it would fail. By specifying a simple SQL statement to obtain all data and disabling query folding, the slicer is now working fine. 

[amitchandak]

@Anonymous , You are taking the first date and the last date on date table or sales Table.  Also, check for Min and Max.

You can get different values when you use it on the sales vs date table. unless you force some filter from sales on date.

Can you share sample data and sample output in table format?

[Anonymous]

@amitchandak , thanks for your reply, to clarify, this is a single table only, there is no relationship to a dedicated date table. It's an output from a web scraper wrting back to Azure SQL. The reason for Direct Query is to allow for immediate data consumption rather than waiting for a data refresh. 

If I import the data in the exact same configuration, the slicer works fine, but I dont want to use import as my users need the data in near real time. 

I just created a copy of the report, changed it to import (leaving everything else the same) and now the date slicer is working as intended. So the question is, how can I get it working with direct query? 

EDIT

It looks liek maybe it is a bug with the out of the box slicer? I just brought in the Microsoft Custom Visual (Timeline 2.1.1) and althouggh not suitable for my usecase, due to its size, it did select the correct start and end dates: