Date is casted as Datetime when filterin

[nimrodsv]

Hello,

This was already asked 3 times before, in 2 cases the users found a workaround that didn't really fixed the issue in PowerBI.

I have a date field in the database (Azure SQL) and PowerBI, while doing a fitler on that field and using a direct query PowerBI is casting a dummy time stamp to a datetime field and comparing it into my date field.

I would like to have PowerBI using the date field as a date when filtering it.

Is there anything I can do from my side in order to resolve?

Or should this considered as a bug that should be fixed from Microsoft side?

This is impacting our performance alot.

See example below

SELECT TOP (1000001) [t1].[contenido_id], 
                     Count_big([t1].[id]) AS [a0] 
FROM   ((SELECT [$Table].[id]                  AS [id], 
                [$Table].[id_estadistica]      AS [id_estadistica], 
                [$Table].[accsub]              AS [accsub], 
                [$Table].[accls]               AS [accls], 
                [$Table].[accad]               AS [accad], 
                [$Table].[contenido_id]        AS [contenido_id], 
                [$Table].[contenido_numero]    AS [contenido_numero], 
                [$Table].[contenido_nombre]    AS [contenido_nombre], 
                [$Table].[contenido_plantilla] AS [contenido_plantilla], 
                [$Table].[contenido_orden]     AS [contenido_orden], 
                [$Table].[idioma]              AS [idioma], 
                [$Table].[recorrido_nombre]    AS [recorrido_nombre], 
                [$Table].[centro]              AS [centro], 
                [$Table].[fecha]               AS [fecha], 
                [$Table].[hora]                AS [hora] 
         FROM   [dbo].[powerbi_logs] AS [$Table])) AS [t1] 
WHERE  ( ( ( [t1].[centro] = N'XXXXXXXXXXX' ) 
           AND ( [t1].[fecha] >= Cast('20180527 00:00:00' AS DATETIME) ) ) 
         AND (( [t1].[contenido_plantilla] IN ( 
                N'Carrusel', N'Panoramica', N'Video' ) 
              )) ) 
GROUP  BY [t1].[contenido_id] 

- I don't want to change my table to Import mode like assigned 'resolved' here:
https://community.powerbi.com/t5/Desktop/DirectQuery-wrong-datetime-format-in-filter/td-p/99409

- I don't want to add an additional dummy datetime field to each date into my db, as 'resolved' here:

https://community.powerbi.com/t5/Desktop/Date-casting-on-DateTime-filter-using-Direct-Query/td-p/623...

Also, I've made sure that the field is assigned as a date in PowerBI and the database.

@umperio 

@Alfredo 

[v-shex-msft]

Hi @nimrodsv ,

You can parametrize your t-sql string based on query parameters, then it should dynamic changes based on your parameters.

let
    Source = Sql.Database("test", "source", [Query="select * from Table1 where id ="&Para1&" and column2 > 12"])
in
    Source

Deep Dive into Query Parameters and Power BI Templates

Regards,
Xiaoxin Sheng

[nimrodsv]

I'm sorry for the late response.

What you are purposing couldn't resolve my issue since i will not do that for every widget i have on the report.

The resolution should come from PowerBI development, as this is a bug.

[nimrodsv]

I would like to give more information and example, I'm including the query that powerbi do and what should be

PowerBI query (after changing to TOP 1000 instead of  1000001)

SELECT TOP (1000) --3:25 Minutes
[t1].[hour]
,COUNT_BIG(*) AS [a0]
FROM ((SELECT
[$Table].[timestamp] AS [timestamp]
,[$Table].[sec_10] AS [sec_10]
,[$Table].[min] AS [min]
,[$Table].[min_10] AS [min_10]
,[$Table].[hour] AS [hour]
,[$Table].[date] AS [date]
,[$Table].[month_name] AS [month_name]
,[$Table].[day_name] AS [day_name]
,[$Table].[WeekNo] AS [WeekNo]
,[$Table].[FirstDayOfWeek] AS [FirstDayOfWeek]
FROM [dbo].[dim_time] AS [$Table])) AS [t1]
WHERE (([t1].[date] < CAST('20190717 00:00:00' AS DATETIME))
AND ([t1].[date] >= CAST('20190703 00:00:00' AS DATETIME)))
GROUP BY [t1].[hour]

What i would expect the query to be like

SELECT TOP (1000) --What is expected to be the query like: 0:46 Minutes
[t1].[hour]
,COUNT_BIG(*) AS [a0]
FROM ((SELECT
[$Table].[timestamp] AS [timestamp]
,[$Table].[sec_10] AS [sec_10]
,[$Table].[min] AS [min]
,[$Table].[min_10] AS [min_10]
,[$Table].[hour] AS [hour]
,[$Table].[date] AS [date]
,[$Table].[month_name] AS [month_name]
,[$Table].[day_name] AS [day_name]
,[$Table].[WeekNo] AS [WeekNo]
,[$Table].[FirstDayOfWeek] AS [FirstDayOfWeek]
FROM [dbo].[dim_time] AS [$Table])) AS [t1]
WHERE (([t1].[date] < ('20190717'))
AND ([t1].[date] >= ('20190703')))
GROUP BY [t1].[hour]