Forum Discussion
Show Date/time based on user's time zone
Hi Anonymous
While M has built-in functions to handle timezones, none of them automatically shifts to the user's. The service uses Utc while Desktop is the user's. M will not know what timezone those datetimes are in so any datetime column converted to datetimezone will be +0 if refreshed in the service. You can convert a datetime column to datetimezone with an offset value (like 10.5 for GMT+10:30) using DateTimeZone.AddZone but knowing what timezone to add is key. Either way, you get to select and load only a specific timezone - Utc or user-defined.
Super UserHi Anonymous
You can use a parameter where the user can select the timezone from and add that to the datetime value. Take note that the switched zone value has to be a measure as a calculated column will not recalculate in response to a slicer selection. A column that will return a distinct datetime must be added to the visual as measures do not have row context. For example, if you add category and category A has two or more datetime values then the measure will not show the correct value. Alternatively, instead of using a slicer you can use USERPRINCIPALNAME() to return the current user's email in the service and use that to filter a predefined table of the user email + the corresponding timezone. Please see attached pbix for the details
Hi DAN,
Thank you for your solution, but I faced another issue.
I can't use measures for graph visuals, for example, the X-axis of a line chart.
Is there any way to update the time zone based on user selection in graphs alongside tables?
- danextian
Hi Anonymous
Your other workaround is to crossjoin all timezones with the distinct values of your datetime columns, create a many to many relationship between the original datetime column and the crossjoined one and use the timezone from the crossjoined table in the slicer.
Crossjoined Table = VAR Timezones = SELECTCOLUMNS ( GENERATESERIES ( -23.5, 23.5, 0.5 ), "TimeZone", [Value] ) VAR DistinctDateTimes = DISTINCT ( 'fact table'[datetime] ) RETURN CROSSJOIN ( Timezones, DistinctDateTimes )Be aware while this is possible, this might negatively impact the performance of your model.
Hi danextian,
Thanks for your help and solutions.
I tried creating a cross-join column but got a "token Eof expected." error.Could you help me with that?
- danextian
It is to be done in DAX as calculated table.
Hi danextian,
Sorry for asking lots of questions, but I'm a little confused.
I couldn't set a many to many relationship here and also I don't know how to connect the time zone column with our crossjoined table.
In the demo file you sent me, I added a new graph to show the switched time zones.
Could you please help me with how I should do this?
I couldn't upload a pbix file here so here's the link to transferxl.- danextian
There's something I miss in the formula. I forgot to add the datetime based on the selected timezone. Should have been:
Crossjoined Table = VAR Timezones = SELECTCOLUMNS ( GENERATESERIES ( -23.5, 23.5, 0.5 ), "TimeZone", [Value] ) VAR DistinctDateTimes = DISTINCT ( 'fact table'[datetime] ) RETURN ADDCOLUMNS ( CROSSJOIN ( Timezones, DistinctDateTimes ), "Switched DateTime", [DateTime] + DIVIDE ( [TimeZone], 24 ) )Please see attached sample pbix