Forum Discussion

apollnor's avatar
apollnor
Advocate II
9 years ago
Solved

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_Norwegia...
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    apollnor

     

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

     

    Best Regards,
    Herbert

  • apollnor's avatar
    apollnor
    9 years ago

    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)

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

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

apollnor's avatar
apollnor
Advocate II
9 years ago

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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    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

    • apollnor's avatar
      apollnor
      Advocate II
      9 years ago

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

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        9 years ago

        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