Top N visual filter does not work correctly with milliseconds in Direct Query mode to Azure SQL

[Anonymous]

In DQ mode when filtered with DateTime which contain milliseconds, no data is returned, because under the hood PBI selects top N values with the first query, then converts it to string internally, and then sends to SQL Server with casting to datetime.
The problem is when converting to string and back, all milliseconds are dropped and if you got not 0 milliseconds in DB, these rows will never be selected.
So, the behaviour is really unusual and strange. Could you note it on TopN filters doc at least?

[v-qiuyu-msft]

hi @Anonymous ,

Would you please share DDL and DML for us to create a sample table? 

Also please clarify how you filter the data in Power BI. It would be better if you could share a video to clarify the issue. 

By the way, the latest Power BI desktop version is 2.66.5376.2161, please try to update to this new version then check if the issue still occurs.  

Best Regards,
Qiuyun Yu 

[Anonymous]

Hi @v-qiuyu-msft , sorry for such a late response.
Sample scripts can be:
CREATE TABLE [dbo].[test](
[DateTime] [datetime] NULL
) 
Insert into [dbo].[test] ([DateTime]) VALUES ('2019-03-19 16:27:30.100')

Then load data via DQ to Power BI.

In the power bi fill table visual with this column and in the Filters pane use TopN filter (select top 1 by latest date).
You will got the empty table and in the Tracing file you can find that while sending query to the SQL Server, datetime is truncated to seconds.
By occasion I am using service account now, so you may contact me by email staydol@gmail.com

[v-qiuyu-msft]

Hi @Anonymous, 

Thank you for your detail information. 

I have reported this issue internally: CRI 111817309. Will update here once I get any information. 

Best Regards,

Qiuyun Yu 

[Anonymous]

@v-qiuyu-msft Thank you very much!

[v-qiuyu-msft]

Hi @Anonymous,

I got information below: 

"This is a limitation by design. Power BI DirectQuery only supports time values at second granularity."

Best Regards,
Qiuyun Yu