Modify Timezone for apps own data power bi embedded model

Hi Don-Bot ,
As lbendlin said  standard and efficient approach to handle time zones in Power BI, especially in multi-user, multi-time-zone scenarios, is to store all datetime values in UTC and let the report user's browser handle the time zone conversion. Here's how you can implement this approach:tandard and efficient approach to handle time zones in Power BI, especially in multi-user, multi-time-zone scenarios, is to store all datetime values in UTC and let the report user's browser handle the time zone conversion. Here's how you can implement this approach:

On the power bi service some times following adjusetment may help ypu, Handling time zone adjustments for a large dataset in Power BI, especially with a star schema and fact-dimension relationships, can be resource-intensive. Here's a streamlined approach to resolve your issue.

Approach 1: Adjust Timezone in the Fact Table (Preferred for Scalability)

Instead of modifying the date dimension, you can adjust the time zone directly in the fact table or at the reporting layer. This avoids overloading the dimension table with large adjustments.

1. Enhanced Measure for Time Zone Adjustment: Simplify and optimize your measure to handle large datasets:

   CreateDateTimeLocal =
   VAR DateValue = MAX(Ticket[datetime_id])
   VAR Year = LEFT(DateValue, 4)
   VAR Month = MID(DateValue, 5, 2)
   VAR Day = MID(DateValue, 7, 2)
   VAR Hour = MID(DateValue, 9, 2)
   VAR BaseDateTime = DATE(Year, Month, Day) + TIME(Hour, 0, 0)
   VAR AdjustedDateTime = BaseDateTime + [SelectedTimezoneOffset] / 24
   RETURN AdjustedDateTime

   • SelectedTimezoneOffset can come from a user-provided parameter or RLS-based user profile.

2. Leverage Parameters for Time Zone Offsets:

   • Create a parameter or lookup table for time zones (TimeZoneOffsets).
   • Map each client or user to their appropriate time zone.

3. Fact Table Adjustment:

   • Keep the adjustment in the fact table, which avoids issues with dimension table scalability.
   • Use this measure directly in your visualizations for context-aware calculations.

Approach 2: Adjust Time Zone in the Date Dimension (Less Recommended for High Scale)

If adjusting the Date Dimension is essential (e.g., for consistent reporting), follow these guidelines:

1. Pre-calculate Time Zone Adjustments:

   • If feasible, adjust the time zones during ETL or pre-load processing rather than in DAX.
   • Add a column for each time zone offset in the Date Dimension (e.g., UTCDateTime, ESTDateTime, etc.).

2. Optimized DAX for Adjusted DateTime: If DAX adjustment is unavoidable, improve the measure:

   AdjustedDateTime =
   'Create DateTime'[Creation DateTime] + [SelectedTimezoneOffset] / 24

   • Use relationships and aggregations carefully to avoid high cardinality joins.

3. Model-Specific Considerations:

   • Ensure datetime_id remains unique in the dimension.
   • Limit DAX evaluation scope by filtering down the dataset using CALCULATE or FILTER.

Best Practices to Handle Large Data:

1. Aggregate Data Where Possible:

   • Summarize the fact table by aggregating time-based metrics (e.g., daily ticket counts) to reduce row counts.

2. Use Direct Query or Incremental Refresh:

   • If data size is a concern, configure DirectQuery for the fact table or enable incremental refresh.

3. Leverage Dataflows for Preprocessing:

   • Use Power BI Dataflows or upstream ETL tools (e.g., Azure Data Factory) to precompute time zone adjustments.

Testing and Validation:

• Apply filters in your reports (e.g., ClientID or UserID) to validate the measure against different time zones.
• Use small datasets for testing and gradually scale up.

If you're still encountering resource errors, focus on optimizing relationships, data cardinality, and aggregation logic. Let me know if you need further assistance!