Direct Query, AzureDW and Regional Date Formats

[jmatta]

I'm running into problems when using FI regional settings with dates. If I try to slice Direct Query dataset with dates (dd.mm.yyyy), I'm getting the error message below. Seems that the SQL query gets the datetime in wrong format?

If I switch the client reginal settings to US, everything works.
I'm not able to use date DAX-functions either with the direct query data. Without filtering or functions it also works.

Error Message:
110802;An internal DMS error occurred that caused this operation to fail. Details: Please use this Error ID when contacting your Administrator for assistance. EID:(3e14eb2919564915ab498a50575bcf3d)
[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]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 IDbCommand interface.
User ID:

[jmatta]

I'm reading data from Azure Data Warehouse with Direct Query - and PowerBI Embedded. The problem is, I'm getting all kinds of error messages with dates. I cannot filter data with datetimes nor dates. I am not able to use date functions either.

However, if I change date formats to US on my PC, everything works (now using dd.mm.yyyy). So it seems that Direct Query with Azure DW doesn't support other regional settings than US.

Only workaround I can come up with, is to build the required date logic to the view inside Azure DW...

[v-haibl-msft]

@jmatta

Before get data, could you please try to change the locale setting in Power BI Desktop.

1. Go to File > Options and settings > Options.
2. Under Current file, select Regional Settings.

3. In the Locale box, select a different locale.

Best Regards,
Herbert

[jmatta]

@v-haibl-msft Changing the PowerBI Locale doesn't fix the problem. Regardless of the locale settings, the date is presented in the locale settings of the client in the UI - and sent to Azure in a format that doesn't work.

Regards,

Jani

[apollnor]

I have this exact same problem, only that my source is my own SQL server:

http://community.powerbi.com/t5/Desktop/Problems-with-DAX-SQL-and-locale/m-p/195659#M86063