topic Re: Problems with DAX, SQL and locale in Desktop

Problems with DAX, SQL and locale

apollnor — Fri, 16 Jun 2017 13:15:44 GMT

Server OS: Windows Server 2016

Server Region & Language: Norway

Server Formats: Norwegian Bokmål (Norway)

SQL Instance Version: Microsoft SQL Server 2016 (SP1)

Instance Collation: Danish_Norwegian_CI_AS

Client Computer OS: Windows 10 x64 Pro

Client Computer Region & Language: Norway

Client Computer Formats: Norwegian Bokmål (Norway)

I'm having trouble with getting the locale settings in PowerBI to work and to be fair, I don't really understand why I have to change them in this case.

I connect directly to the SQL Server (DQ), and I change the locale of the PBIX itself to Norwegian, as well as all date columns I use in all fact and dim tables.

Having done this, the following measure still fails with the good old (Could not convert nvarchar to date format...)

CurrentBudget = SUMX(FILTER(DimBudget,DimBudget[TimeAltKey] <= TODAY()),DimBudget[BudgetTarget])

Changing the query to a more advanced version, using the date dimension to iterate returns the sum of the budget for as far as the filter context of the report allows, for example, the whole of 2017, even if I have limited the result to the current date.

CurrentBudget = SUMX(FILTER(DimBudget,RELATED(DimTime[FullDate]) <= [Today]),DimBudget[BudgetTarget])

If I change the formats on my client computer operating system to use American date format and punctuation, it all works...

The original content of the data warehouse come from CSV-exports and are loaded into the table in the database using SSIS packages that I've built using Visual Studio 2015.

Re: Problems with DAX, SQL and locale

apollnor — Mon, 19 Jun 2017 08:07:21 GMT

If I do a simple test and import the data from only the table the measure needs to work with, I have no problems using Norwegian Locale. However, If I do the same, only in DQ mode, I'm back to getting the error message...

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value... The exception was raised by the IDataReader interface.

As the source SQL-server is my own, I have also tried to change the language of the SQL instance to my own native, just to check if it had any effect, but it did not.

I found a similar post on the forums, where the exact same problems are described: http://community.powerbi.com/t5/Desktop/Locale-settings-not-working/m-p/49588/highlight/true#M19771

Re: Problems with DAX, SQL and locale

v-jiascu-msft — Mon, 19 Jun 2017 09:18:35 GMT

Hi apollnor,

According to my test, the dates will convert automatically if they are in the formats that can be converted. Sometimes the convention will return wrong date but it works unless the data is out of range. It’s hard to say what went wrong. Please check these things below.

Please change the “Data type” of the column into Date in “Modeling” ribbon.
Please check the dates in DB if some of them is out of range. For instance, “2017-02-30” can’t be converted.
Change data type of that field into “Date” in the DB if possible. So we can make sure Dates are correct in the DB.
Please post some snapshot here. (Error message, data in the DB, data in the report.)

Best Regards!

Dale

Re: Problems with DAX, SQL and locale

apollnor — Mon, 19 Jun 2017 10:01:16 GMT

I forgot to mention it in my original post, but I've already tested changing the data type of the date column in PowerBI using both Norwegian and English American locale. PowerBI correctly interprets the column as a date column upon querying the database.

I've also tried changing the locale of the whole model between Norwegian and English American, with different combinations on the column locale. And measures in direct query mode are unrestricted.

There should not be any problems with the way the column data types are defined in SQL. Here is an image of the table used in the example measure:

So the dates in the database are automatically recognized as dates in PowerBI or at least so it says.

Trying to create the measure works just fine:

CurrentBudget = SUMX(FILTER(DimBudget;DimBudget[TimeAltKey] <= TODAY());DimBudget[BudgetTarget])

Adding it to a visualization gives me this:

Change the locale formats of my local computer where I'm running Power BI Desktop to English (United States):

Refreshing the model in PowerBI:

So, I've done no changes on the SQL side to get this working. I've tried changing the locale of the whole PBIX-file, and the columns to both English and Norwegian without it helping. And if I change from DQ to Imported it works just fine without changing any locale settings in PowerBI.

As an additional test, I connected to the old data warehouse, where I know that things have worked in the past, and I get the same issue when creating the same measure but on a different table. Change my computers locale format to English, and it works.

I've even tried restoring the DWH database on an instance on the same computer as where I'm running PowerBI, and the exact same problem occurs, with the exact same remedies for getting it to work.

I've also tried creating a measure as so: DD = Today(), and changed the format of that measure to match the format of the time column, and using that measure instead of today() in my original measure. Works fine in American, doesn't work in Norwegian, but the DD measure iself works in both OS locale format settings.

Re: Problems with DAX, SQL and locale

v-jiascu-msft — Tue, 20 Jun 2017 02:50:12 GMT

Hi apollnor,

I reproduced this problem. The trick is the formats of date are different from each other of the DB and PBI. The workaround is changing the format of the DB or the PBI to make them same. This expression can show up the default format of the DB.

select name,alias, dateformat 
    from syslanguages 
    where langid= 
         (select value from sysconfigures where comment='default language');

I will submit a report internally and will post here when I get new information.

Best Regards!

Dale

Re: Problems with DAX, SQL and locale

apollnor — Tue, 20 Jun 2017 07:54:41 GMT

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.

http://community.powerbi.com/t5/Issues/Direct-Query-AzureDW-and-Regional-Date-Formats/idi-p/158421#comments

Thanks for bringing this in :)

Re: Problems with DAX, SQL and locale

v-jiascu-msft — Tue, 20 Jun 2017 10:04:11 GMT

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

Re: Problems with DAX, SQL and locale

apollnor — Tue, 20 Jun 2017 10:11:03 GMT

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 :)

Re: Problems with DAX, SQL and locale

v-jiascu-msft — Tue, 20 Jun 2017 11:21:21 GMT

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

Re: Problems with DAX, SQL and locale

v-haibl-msft — Thu, 22 Jun 2017 02:25:50 GMT

apollnor

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

Re: Problems with DAX, SQL and locale

apollnor — Thu, 22 Jun 2017 06:09:02 GMT

I'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, @money

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO TestDB.dbo.test VALUES (@date,@datetime,@money)
FETCH NEXT FROM test_cursor
INTO @date, @datetime, @money
END

CLOSE test_cursor
DEALLOCATE test_cursor

Then 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.

Re: Problems with DAX, SQL and locale

v-jiascu-msft — Mon, 26 Jun 2017 12:18:11 GMT

Hi apollnor,

We still work on it. Thanks for your patience.

Best Regards!

Dale

Re: Problems with DAX, SQL and locale

v-haibl-msft — Thu, 06 Jul 2017 01:45:31 GMT

apollnor

The fix will be available in August 2017 version of Power BI Desktop.

Best Regards,
Herbert

Re: Problems with DAX, SQL and locale

apollnor — Mon, 24 Jul 2017 06:13:25 GMT

Thanks guys :)

Re: Problems with DAX, SQL and locale

cxtale — Thu, 17 Aug 2017 12:44:51 GMT

I still have this issue with a time slice and I'm on Version: 2.49.4831.521 64-bit (August 2017) ???

Re: Problems with DAX, SQL and locale

apollnor — Mon, 21 Aug 2017 08:55:37 GMT

cxtale: That's strange, after the update (i just downloaded it as i read what you wrote), this seems to have been fixed for me.

Version: 2.49.4831.521 64-bit (august 2017)