Forum Discussion
Problems with DAX, SQL and locale
Does this mean that the locale settings in PBID are not working properly at the moment when running queries directly to the DB? I'm pretty sure this problem existed before but was remedied when the locale settings were introduced, but I might be incorrect.
Here are the results from your query on my server (and my client's instance):
When you say I can change the format in PBI as a workaround, what exactly do you mean step by step? For now, the only capable solution I've found is running the report in imported mode.
jmatta seems to have the same problem, only his source is in the cloud.
Thanks for bringing this in :)
Hi apollnor,
Actually, you had tried it.
"Change the locale formats of my local computer where I'm running Power BI Desktop to English (United States):"
The format of date in DB is "mdy" while in the client is "dmy". This is the root cause. Changing it in the DB may avoid changing it in many clients. BUT THIS IS NOT A GOOD IDEA DOING IT IN A PRODUCTION DB. Usually, we create a new one with the proper format if possible. Please attention: This is not an advice from a database professional.
Best Regards!
Dale
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 :)Hi apollnor,
I will post here as soon as I get any updates. You could try it to get it to work for now.
Best Regards!
Dale
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,
HerbertI'm guessing the error message the team was looking at here, was the one you produced during the reproduction of my problem? If so did you set your date column ('test'[datetimes]) to be NVARCHAR, or is the team just assuming this base on the error message?
I'm asking because I posted a screenshot of the column types on the database I'm connected to, where you can without a doubt see that the date column is defined with the date data type.
Further tests I've done have involved publishing the report with a live query to the DB via a Data Gateway on the SQL Server, and then opening it with a browser on the same machine as where I use PBID. No problem at all, everything works. The problem only persists in PBID.So in order to make sure this doesn't have anything to do with me using a 'date' column instead of a 'datetime' column I've transported all the data from DimBudget to a test database on the same server, where I now have the following columns:
Extract/Load Script (FYI):
SET NOCOUNT ON;
DECLARE @date date, @datetime datetime, @money money;
DECLARE test_cursor CURSOR
FOR
SELECT [TimeAltKey] as [date],CONVERT(datetime,[TimeAltKey],101) as [datetime] ,[BudgetTarget] as [money]
FROM [PeanutsY].[dbo].[DimBudget]
ORDER BY [TimeAltKey]OPEN test_cursor
FETCH NEXT FROM test_cursor
INTO @date, @datetime, @moneyWHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO TestDB.dbo.test VALUES (@date,@datetime,@money)
FETCH NEXT FROM test_cursor
INTO @date, @datetime, @money
ENDCLOSE test_cursor
DEALLOCATE test_cursorThen I create a brand new PBIX, connect it directly to the new database, load the table, edit the 'date' and 'datetime' column using locale (American English), and create my measures:
I am the DBA, as well as the BI Admin, the storage admin, the server admin and whatever else admin role exists; I do everything. There is no one for me to talk to but myself, and if I did heads would explode :D
EDIT: I assumed the last reply was from Dale, but I see now that it was from Herbert.