Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am running a report off of an SQL database that must remain in directquery mode, and is populated by proprietary software that I cannot change.
All datetime is devoid of timezone, so PowerBI defaults to +00:00, which is UTC time. I need all dates to be on EST time zones for relative filters to work.
So far, I've tried:
Any other suggestions? I find it completely stupid, bordering on useless, to not be able to set timezones on reports or use the end user's computer's timezone. Power BI is now 7 years old, surely this shouldn't be this hard to implement?
I did a simple test with a published Power BI datsets and converting it to a local model (DQ mode). I was able to add a DAX column with this formula to adjust all the datetime values to 5 hrs earlier. Would the same work for you?
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
he tried, but he wants to be able to use it for a filter also, and as a measure you cant use it for filtering, also he told dual mode DQ/Import wasnt a option for him, so thats why I suggested him to do a direct sql statement when calling the DQ source, cant think of a work around other tham that cause those 2 limits he have there.
Proud to be a Super User!
use a sql statement like this on the query call for the direct querye sql:
declare @utc_date datetime = getdate()select @utc_date as utc_time_zone, dateadd(hh, datediff(hh, getutcdate(), getdate()), @utc_date) as local_time_zone
like this you can send directly to the server a query that change that column from utc to a specific time zone
Proud to be a Super User!
Good morning,
I've not been able to work on this until just now
So, I've been able to get to the point where I can enter the SQL statement.
As of right now, I have 6 different columns, spread over 4 tables. Current format of all those date/times are as follows (this is copied straight from the SQL database)
2020-06-02 13:16:30.0000000
The table names and columns where I'd need the timezone info are as follows:
Table name | Column name |
DataPartCell1 | EndTime |
OeeDefectHistory | Time |
OeeFaultHistory | StartTime |
OeeFaultHistory | StopTime |
OeeModeHistory | StartDate |
OeeModeHistory | StopDate |
I am not super well versed in SQL, is there a query that would allow me to add the timezone info in my columns as they get pulled into PowerBI? Would that cause any data refresh slowdown?
I appreciate all the help you've given me so far.
Hi @icbd
You may refer to blogs as below, I hope they can help you solve your problem.
For reference:
Power BI Convert UTC to Local Time
Timezone conversion in Direct Query
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
How do I get to that page once the data source has already been setup? I can't seem to get there.
Edit: I got there, but the SQL statement box is greyed out.
try setting the model to a dual mode : direct query and import mode, this should let you change the column type by region , also can try setting a sql statement on the source call to do the server manage the time zone changing,
Proud to be a Super User!
I'm not sure what you mean by dual mode. The model is already a mixed model as I have a few automatically generated tables to apply different filters to the report.
The dataset is much, much larger than the datacap offered by PowerBI and growing larger by the minute. Importing the table is not an option. Thus it must remain in directquery.
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |