Hi all,
I have been looking into datetime conversion solution in Power BI Report for both direct query and import mode storage.
I'm Embedding these report into my web application with Embed for Customer Solution.
my current method-[DAX calculation in Measure].
1. source: SQL Server
2. in web application handling 30 timezone (seven abbreviation) and also user can change thier timezone and in application screen user can see the datetime converted to that timezone. its a feature client wanted and I'm trying to bring that feature in report level also .
3. That said, I have created the measure which calaculates the DST and Non-DST range for each year from a datetime column passed to it. along with DST_FLAG as some region not follow dst but comes under same abbreviation (ex: pacific/Honolulu, HST and America/Adak, HST/HDT)
4. this measure then i added in table visuals along with other information that i want to show to user.
The measure converts the datetime to user timezone as i was passing the zone information using disconnected 'Dim_User_TimeZone' table in direct query mode with RLS (ex: [USER_ID]=USERNAME()) and role name is 'USER
note: if user changes the timezone info in web application I can fetch latest zoneinformation as this table in DQ.
5. from application in generateToken I will pass the current logged in user id with role name 'USER', so this will pass to the timezone table i will get the current logged in user timezone info like zonename,abbreviation,dst_flag.
6. below is the measure i used for both Direct query and import mode storage report.
CallDate =
VAR SelectedDate = MIN(Fact_Call_Track[CALL_DATE])
RETURN
IF(
ISBLANK(SelectedDate),
BLANK(),
VAR TimeZone = MIN(Dim_User_TimeZone[Abbreviation])
VAR DSTFlag = MIN(Dim_User_TimeZone[DST_FLAG])
// DST boundaries
VAR Year_ = YEAR(SelectedDate)
VAR MarchFirst = DATE(Year_, 3, 1)
VAR FirstSundayInMarch = MarchFirst + MOD(7 - WEEKDAY(MarchFirst, 2), 7)
VAR DSTStart = FirstSundayInMarch + 7 + TIME(2, 0, 0) //adding 7 To get the second sunday of March
VAR NovFirst = DATE(Year_, 11, 1)
VAR FirstSundayInNov = NovFirst + MOD(7 - WEEKDAY(NovFirst, 2), 7)
VAR DSTEnd = FirstSundayInNov + TIME(2, 0, 0)
VAR IsInDST = SelectedDate >= DSTStart && SelectedDate < DSTEnd
VAR TotalOffsetMinutes =
SWITCH(TimeZone,
"CST", -360,
"EST", -300,
"HST", -600,
"AKST", -540,
"PST", -480,
"MST", -420,
"IST", 330,
0
) +
IF(DSTFlag = 1 && IsInDST, 60, 0)
VAR AdjustedDate = SelectedDate + (TotalOffsetMinutes / 1440.0)
RETURN
AdjustedDate
)
schema of 'Dim_User_TimeZone' looks like below:
USER_ID TIME_ZONE_NAME Abbreviation DST_FLAG
test_user_1014 Asia/Kolkata IST 0
test_user_2177 America/Adak HST 1
test_user_4459 America/Phoenix MST 0
test_user_547 Pacific/Honolulu HST 0
test_user_7135 America/Chicago CST 1
test_user_924489 America/Los_Angeles PST 1
The problem:
since it is a measure i can't use it in slicer and X/Y-axis of the chart for trend alanysis and also some of crucial datetime
difference w.r.to user timezone like day bucket, hour bucket to display the count (ex: if i want to show the count of task over hour bucket like 'Overdue', <12hr, <24hr >72hr.)
Second Method [with parameterized dynamic query ]:
1. Created the TZ table with column ZoneName and TimeZoneId at Power Query using option Enter Data.
this table contains both IANA database zone name and windows registry names.
2. created the parameter named 'Zonename' with default value 'Asia/Kolkata'
3. Filtered the TZ table ZoneName column with that parameter.
4. applied the changes and in data model level i have used the Bind to Parameter option for ZoneName column from TZ table
5. note: TZ table is disconnected table and has appx. 460 records mapping b/w IANA and windows registry
TZ table looks like below
ZoneName TimeZoneId
Etc/GMT+12 Dateline Standard Time
Etc/GMT+11 UTC-11
America/Adak Aleutian Standard Time
Pacific/Honolulu Hawaiian Standard Time
Pacific/Marquesas Marquesas Standard Time
America/Anchorage Alaskan Standard Time
America/Los_Angeles Pacific Standard Time
America/Boise Mountain Standard Time
6. in web application config payload i have passed the basic filter using client API see below:
let config = {
type: 'report',
tokenType: tokenType == '0' ? models.TokenType.Aad : models.TokenType.Embed,
accessToken: accessToken,
embedUrl: embedUrl,
id: embedReportId,
permissions: permissions,
settings: {
panes: {
filters: {
visible: true
},
pageNavigation: {
visible: true
}
},
bars: {
statusBar: {
visible: true
}
}
},
filters: [
{
$schema: "http://powerbi.com/product/schema#basic",
target: {
table: "TZ",
column: "ZoneName"
},
operator: "In",
values: ["America/Adak"]
}
]
};
7. Then i have created the direct table for which i want to convert the datetime fields, with below query format
table name: "Converted_Datetime"
let
Source = Sql.Database(Server, Database, [Query="
SELECT
CTRK.CALLTRK_IDN
,CTRK.CALL_DT AT TIME ZONE 'UTC' AT TIME ZONE '"&List.First(Table.Column(TZ,"TimeZoneId"))&"' AS CALL_DATE
,CTRK.FOLLOW_UP_DT AT TIME ZONE 'UTC' At TIME ZONE '"&List.First(Table.Column(TZ,"TimeZoneId"))&"' AS NEXT_FOLLOWUP_DATE
FROM
CALLTRK CTRK WITH(NOLOCK)
WHERE CTRK.ENTITY_ACTIVE = 'Y'
",CreateNavigationProperties=true])
in
Source
8. from this i will handle the conversion at sql level usingAT TIME ZONE function, then build a relationship for my fact table(imported table)with calltrk_idn column from Converted_Datetime and data flows from Converted_Datetime to "Fact_Call_Track" and this will converts import mode report to mixed mode.
9. for reports with direct query I can directly call zoneId from TZ by concatenating the expression(AT TIME ZONE 'UTC' AT TIME ZONE '"&List.First(Table.Column(TZ,"TimeZoneId"))&"') in the respective table sql query.
10. now datetime converts to user timezone at column level and now i can use at slicer, line,bar chart for trend analaysis and also datetime difference calaculation w.r.to user timezone.
It may look a bit complex, but I have implemented this approach and tested it successfully with concurrent users, as well as with the same user switching between different timezones. In all cases, the datetime column is correctly converted to the user’s selected timezone.
Note: One of the key requirements is to avoid on-demand dataset refresh when a user updates their timezone at web application in Import mode Report. With the measure-based approach, the converted datetime is displayed at visual rendering time (for table visuals), so no refresh is required. I also explored a second approach using an alternative method to address this requirement.
I’m looking for guidance or best practices to handle this scenario more cleanly or in a recommended way.
