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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Alfredo
Regular Visitor

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

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 @Alfredo,

 

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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