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

I see two major pain points:

1 - You need to do the time zone correction before you split the date and time up.

The tz correction needs to be done in a calculated column (that is dynamic to the user), then the calculated column can be split into date and time portions just by repeating the measure and coverting to date or time. Those columns can then be joined to the separate date and time tables.

2 - Direct query is restrictive

You cannot use the offset related to the user in the user table, and you can't use an independent offset chosen via a "What If" parameter set up.

So that means getting the correct time zones into the reading table, as a column, in a way that allows two users in different time zones to view the same sensor id (rls filter) at the same time, and get a personalized view.

One way to do that is to duplicate the readings record set for each user preference time zone.

This will explode your data, of course, but with the restrictions on direct query, I'm not sure a compact solution is available. Hopefully I am wrong!

Example:

If you have sensor A, with 4 users with unique time zone preferences, you would need four sets of readings unioned together; with one of the potential time zones added to a "TZOffset" column for each set.

The RLS can now handle the sensor ID filter and the time zone filter. This allows two users of the same sensor with different time zone needs to view the same data with a personalized view.

And, with the offset number in the Readings table, you can do the calculated column to get a corrected datetime fairly easily. From there, you can go back to #1 and also have your separate date and time tables.

If I come up with anything else I will let you know.

Juicy problem though, thanks for bringing it to the community!