Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Date casting on DateTime filter using Direct Query

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

2 REPLIES 2
v-piga-msft
Resident Rockstar
Resident Rockstar

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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.

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.