Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Also, I've made sure that the field is assigned as a date in PowerBI and the database.
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
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.
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]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.