Difficult Question: Dynamic Time Zones with Power BI Embedded, Direct Query, and RLS

Here’s my idea. 

 

Create a dim date table at the hour level of granularity (24 rows per day with the key on date/hour utc). Store the minutes (and seconds) in the fact table only. Create a second fact table that mirrors the date table key column, with a column that contains one row for each time zone needed per UTC time.  Something like

 

Table:DateAdjust

 

DateHourUTC

TimeZoneID

adjustedDateTime

 

Eg 

2 Feb 2019 0100, -2, 1 Feb 2019 2300

 

so if you have 7 time zones to cater for, your fact table will be 7x larger than the date table. 

 

Turn on bidirectional filtering to the date table. 

 

Join the timezoneID column to a user security fact table with the USERNAME and timezone. 

 

User security fact table filters the DateAdjust table so they can only see their own timezone columns. This table filters the date table in UTC, which in turn filters your fact table