Forum Discussion
Power Bi Dynamic Time Zone Transformation
Thanks Musab-Ali for the follow-up, I completely understand your scenario now. Since your dashboard depends on the User_Joined_Date field and you need full interaction with a third-party date picker and slicers, the best way to support dynamic time zone switching (without breaking model relationships) is to handle the timezone transformation at the data level instead of relying on dynamic measures.
Here’s the solution that works perfectly with slicers, relationships, and full dashboard interactivity:
- In Power Query, create multiple versions of the Users table — one for each timezone you want to support (e.g., PST, UTC, IST, etc.).
- In each copy, add a new column that adjusts the User_Joined_DateTime_UTC based on that timezone's offset.
- Add a [TimeZone] column to identify the timezone of each record.
- Append all these tables into a single combined table (Users_AllTZ).
- In your model, relate the User_Joined_LocalDate (from the adjusted datetime) to your existing Date table.
- Add a slicer using the [TimeZone] column. When a user selects a timezone, the whole dashboard (including visuals and the date picker) will reflect the adjusted local time.
This approach avoids the limitations of dynamic measures, ensures that your date picker continues to work properly, and lets users switch timezones across the entire dashboard without needing to duplicate visuals or reports.
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.Best Regards,
Tejaswi.
Community Support
Thank you, Tejaswi, for the detailed and well-explained solution.
The use of TREATAS() and timezone offsets in the measure is a smart approach and works well in many scenarios.
That said, I’m still facing a few limitations when trying to implement this in our case:
Relationship Limitations with Date Picker:
We are using a third-party date picker that interacts with our User_Joined_Date through a model relationship. Since the LocalDate in your solution is created inside a measure, it cannot be part of a relationship. This means the date picker can't properly filter or interact with the calculated date, which is critical in our report setup.
Measure and Column Scope Issues:
Measures can't be used in relationships or slicers, which breaks the link between the Date table and the user data when working with local time.
Similarly, using calculated columns with fixed offsets won’t solve the issue either — because once calculated, those columns are static and can’t be reverted or dynamically changed when a user switches timezones.
Dashboard-Wide Timezone Switching Requirement:
Our entire dashboard is built using User_Joined_Date in UTC. What we're trying to achieve is:
When a user views the report in UTC and selects PST from a slicer, the full dashboard should update to reflect PST time.
Then, if the user switches back to UTC (or any other timezone) from the same slicer, the dashboard should convert back accordingly.
This dynamic switching is key we don’t want to build or duplicate visuals or reports per timezone.
So far, I haven’t been able to get the measure-based approach to support this level of interactivity and model integration.
Do you have any suggestions on how we can achieve fully dynamic timezone switching (with slicer support) while keeping relationships and the date picker functionality intact?
Really appreciate any ideas or further direction on this.
Thanks Musab-Ali for the follow-up, I completely understand your scenario now. Since your dashboard depends on the User_Joined_Date field and you need full interaction with a third-party date picker and slicers, the best way to support dynamic time zone switching (without breaking model relationships) is to handle the timezone transformation at the data level instead of relying on dynamic measures.
Here’s the solution that works perfectly with slicers, relationships, and full dashboard interactivity:
- In Power Query, create multiple versions of the Users table — one for each timezone you want to support (e.g., PST, UTC, IST, etc.).
- In each copy, add a new column that adjusts the User_Joined_DateTime_UTC based on that timezone's offset.
- Add a [TimeZone] column to identify the timezone of each record.
- Append all these tables into a single combined table (Users_AllTZ).
- In your model, relate the User_Joined_LocalDate (from the adjusted datetime) to your existing Date table.
- Add a slicer using the [TimeZone] column. When a user selects a timezone, the whole dashboard (including visuals and the date picker) will reflect the adjusted local time.
This approach avoids the limitations of dynamic measures, ensures that your date picker continues to work properly, and lets users switch timezones across the entire dashboard without needing to duplicate visuals or reports.
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.Best Regards,
Tejaswi.
Community Support
Hello Tejaswi,
Thanks a lot for your help!!!
I tested on a mini dataset, and it works as per the requirement
Just wanted to check on Live
Once again, thank you so much for your help
Best Regards,
Musab Ali