Forum Discussion
Difficult Question: Dynamic Time Zones with Power BI Embedded, Direct Query, and RLS
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...
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
RETURN
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] ?