cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
apollnor Regular Visitor
Regular Visitor

Re: Problems with DAX, SQL and locale

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.

 

SQLTABLE.PNG

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:

 

SQLTABLETEST.PNG

 

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:

 

test.png

 

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 😄

 

EDIT: I assumed the last reply was from Dale, but I see now that it was from Herbert.

v-jiascu-msft Super Contributor
Super Contributor

Re: Problems with DAX, SQL and locale

Hi @apollnor,

 

We still work on it. Thanks for your patience.

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
v-haibl-msft Super Contributor
Super Contributor

Re: Problems with DAX, SQL and locale

@apollnor

 

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

 

Best Regards,
Herbert

View solution in original post

apollnor Regular Visitor
Regular Visitor

Re: Problems with DAX, SQL and locale

Thanks guys 🙂

cxtale Visitor
Visitor

Re: Problems with DAX, SQL and locale

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

apollnor Regular Visitor
Regular Visitor

Re: Problems with DAX, SQL and locale

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

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 292 members 2,881 guests
Please welcome our newest community members: