I've been turning this thing over in my mind for the past week. Here's the challenge, and I'll state it from the perspective of a report user ...
"As a user I need to be able to see my data, on a report, in the time zone of my choosing - for example if I set my time zone preference to 'UTC -5' I expect data to be shown as if it were being reported from that time zone."
A few things you need to know.
Things we've tried so far.
Other *specific* thoughts.
Other *random* thoughts.
Here seems to be the rub. To have a set of dates and times to show on a report, you must have those dates and times in a table in the model. In other words they can't just be magically rendered on the spot. At the same time what I need is a layer that is rendered at "run time" based on the preference of the report user.
Interested to hear the community's thoughts.
PS I can't seem to change my default signature which says I'm VP of Operations at PowerPivotPro. That's not up-to-date. I'm now the CEO at Conserv, a startup focused on sensors and software to better preserve art and cultural collections.
To have a set of dates and times to show on a report, you must have those dates and times in a table in the model. In other words they can't just be magically rendered on the spot. At the same time what I need is a layer that is rendered at "run time" based on the preference of the report user.
Why must you have those [timezone-adjusted] datetimes in a table in the model [in Power Query, yes?] Why can't a DAX measure and calculated table be used such as: ???
// Make a table for a slicer of timezone offsets: 'USER_REPORT_SELECTOR_CALCULATED_TABLE' = GENERATESERIES(-12, 12, 1) // Make a measure that combines the data and the user selected offsets or default offset [USER_ADJUSTED_TZ] := VAR WHATEVER_IS_YOUR_DATA = CALCULATE ( 'IoT_product'[timepoint], FILTER ( ALLSELECTED ( 'IoT_product'[timepoint] ), NOT(ISBLANK('IoT_product'[timepoint]))) ) VAR WHATEVER_IS_YOUR_USER_DEFAULT_PREFERRENCE = SELECTEDVALUE('PQ_USER_RLS'[default_tz]) VAR WHATEVER_IS_YOUR_USER_SELECTED_PREFERRENCE = SELECTEDVALUE('USER_REPORT_SELECTOR_CALCULATED_TABLE'[USER_REPORT_SELECTOR_CALCULATED_TABLE], WHATEVER_IS_YOUR_USER_DEFAULT_PREFERRENCE) RETURN WHATEVER_IS_YOUR_DATA + WHATEVER_IS_YOUR_USER_SELECTED_PREFERRENCE
The above DAX should have
IF(ISBLANK(WHATEVER_IS_YOUR_DATA),"N/A", WHATEVER_IS_YOUR_DATA + WHATEVER_IS_YOUR_USER_SELECTED_PREFERRENCE)
But the question is basically why not have a DAX measure of 'your data'[time] + [offset] ?
Hey Avi, your answer was the closest solution I have found to cross time zone reporting. Your pbix file can be downloaded in the link you provided, however, the pbix file takes data form an excel file which I wasnt able to download. Is it possible to get this data from your drive? it would help me a lot.
An alternative can be to explaining or giving a sample of what data that excel contained so we can make sense of the M language within the pbix file.
Here is what I got: Download Files
(Huh...I was hoping the forum would have an option to upload file. But I didn't find it. Above is a link to my OneDrive).
Below is a quick video with a bit more info about the solution.
Is the sample file from your video still available for this issue? I have the exact same requirement for a solution we are building where we need to present Contact Center data across timezones.
I posted a similiar question to the community, but just came across your post today.
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!
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!
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.