Forum Discussion
Problems with DAX, SQL and locale
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.
15 Replies
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
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
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.
