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