Date casting on DateTime filter using Direct Query

[Anonymous]

I'm having some performance issues with direct query. Profiling the queries executed on the Azure SQL, I've seen that the date filter added to PowerBI is casting to datetime when executing the query:

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've tried to execute the same query casting to date instead of datetime([t1].[fecha] >= Cast('20180527' AS DATE)) or without casting ([t1].[fecha] >= '20180527 00:00:00') and the performance is much better.

In PowerBI Query Editor, the column type is marked as Date. Is there any way to avoid PowerBI making this casting?

Thanks!

Regards, Alfredo

[v-piga-msft]

Hi @Anonymous,

If I understand your scenario correctly that your date column in SQL is datetime type and you want to get the date type when load in Power BI. However, when you cast the data type to date, it still load the date column with datetime time?

If it is, I could reproduce your scenario by my test.

I'm afraid that there is no good ways for us to disable this mapping currently. 

Here is a workaround that you could create a custom column in Query Editor with the query below to get the date column.

custom= Date.From([datetimecolumn])

Best Regards,

Cherry

[Anonymous · Accepted solution]

Not exactly. In PowerBI and Azure SQL the column is a Date type column.

The problem was when adding the date filter to PowerBI, when I selected a date range with the slider, and I see the executed queries against the SQL server, the query has a cast to datetime.

Never the less, I've solved the problem adding a datetime column to the database and the PowerBI. The cast clause keeps in the queries, but now, as the column type is the same as the cast, I've no performance issues.