Forum Discussion
Problems with DAX, SQL and locale
I see. I am the one building the DWH so changing it wouldn't be a major problem, just time-consuming and perhaps changing the defaults of the DWH will affect something else in the future, so I would rather stick to having consuming applications adapt to the DB time format.
But am I correct in assuming that the locale settings in the PBIX or locale transformed date column should adjust for this, however, they are not, and the team will have a look at it? Not sure if I should keep working on a permanent workaround or wait for a new release, see :)
I’ve got response from the Product Team.
Based on the error message, the column 'test'[datetimes] is of data type NVARCHAR that cannot be converted to DATETIME on SQL Server side.
Unlike Import models where calculations are performed on PowerBI side, DirectQuery models push as much calculation to the underlying database as possible. In this case, the comparison operation is pushed to SQL Server and it's up to SQL Server to convert NVARCHAR to DATETIME based on its own setting.
You needs to work with your SQL Server DBA to find out the string format of the column and how to extract DATETIME values from it. As a general advice, you should work with your DBA to find out if they can create a DATETIME column directly inside the SQL Server database to have the best performance since you want to filter by that column, SQL Server cannot deliver the best query performance if a filter operation needs to perform data type conversion first.
Best Regards,
Herbert