Forum Discussion
Modify Timezone for apps own data power bi embedded model
Hi Don-Bot ,
If you need the UTC_Local column to reflect the local time zone of the device or a specific time zone, here are your options:
Option 1: Explicit Time Zone Conversion
Instead of relying on DateTimeZone.ToLocal, explicitly set the desired time zone conversion using DateTimeZone.SwitchZone. For example:
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "PST", each DateTimeZone.SwitchZone([UTCDateTime], -7))
Added the (-7) for switch to PST, This ensures that the PST conversion is consistent regardless of where the report is run.
// I often use this method to get the Local refresh time on service
Else you can parameterze the number as follow,
Option 2: Parameterize Time Zone Offsets
Create a parameter for the desired time zone offset and use it in DateTimeZone.SwitchZone. This allows you to dynamically adjust the time zone without modifying the code.
Steps:
- Create a parameter in Power Query for the offset (e.g., TimeZoneOffset).
- Use the parameter in your SwitchZone formula:
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Dynamic_Local", each DateTimeZone.SwitchZone([UTCDateTime], TimeZoneOffset))
Thank you again SacheeTh ,
Let me know if I'm understanding you correctly. But from what I see is the only way to setup different timezones in my model to pre-configure them with extra columns such as "CST, PST, EST"?
I've been able to get the parameter stuff to work but the problem is one model can have users across different timezones. So I can't hard code a parameter into it. The closest thing I could probably do is add the columns for each timezone. Since it appears it's not auto detecting or changing timezones with the methods I've tried above.
Hi Don-Bot ,
Yes, your understanding is correct. Unfortunately & as far as I know, Power BI does not natively auto-detect or dynamically adjust time zones for individual users in the Power BI Service on the report end. If your model has users across multiple time zones, pre-configuring time zones with extra columns (e.g., "CST", "PST", "EST") is one of the most straightforward approaches.That's the only this that pop to me right now, other than user wise RLS or OLS.
Implement Row-Level Security (RLS)
If each user's time zone can be determined from their identity (e.g., region in a database), you can implement Row-Level Security to filter or adjust time zones dynamically based on user roles.
Ah on the 2nd through, you can set up a DAX for that too, I know you also have came a cross on this for sure. Anyway I'll add the steps here. 😄
Use DAX for Dynamic Calculations
If you need user-specific flexibility, consider calculating offsets in DAX. While this won't detect the user's system time zone, you can provide a slicer or control for users to select their time zone.
- Create a Time Zone Table:
- A simple table mapping time zones to that has all the offsets:
Time Zone | Offset EST -5 CST -6 MST -7 PST -8
- A simple table mapping time zones to that has all the offsets:
- Create a Slicer:
- Add a slicer to the report for users to select their time zone.
- Create a DAX Measure:
- Use the selected offset to calculate the adjusted time:
AdjustedTime = VAR SelectedOffset = SELECTEDVALUE(TimeZone[Offset], 0) RETURN UTCDateTime + (SelectedOffset / 24)- Display this adjusted time in visuals.
- Create a Time Zone Table: