Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

Reply
E_K_
Helper III
Helper III

How to create bookmarks/buttons to switch timezones of a calendar visual?

‎12-20-2022 06:29 AM

Hi, I am working on a calendar for events that defaults to ET. Users would like to be able to switch to different timezones eg GMT and SGT. 

 

I have no problem adding the new timezones as different columns but I know switching out fields from the visual will not persist across bookmarks.

 

Anyone have a way of explaining how field parameters could help? I;ve qatched a few videos on it but amn't quite able to apply to my own report as I haven't found with with a date use case.

 

Edited to add sample data and query. 

Unable to share a sample file - here is some of what the data looks like and a santised query: 

let
Source = Excel.Workbook(File.Contents("H:\Calendar Export 081922.xlsx"), null, true),
#"Grid Results_Sheet" = Source{[Item="Grid Results",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Grid Results_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"news_id", Int64.Type}, {"start_time", type datetime}, {"end_time", type datetime}, {"type", type text}, {"inactive_from_time", type datetime}, {"visibility_ind", type text}, {"modified_time", type datetime}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Type", each if [type] = "A" then "Announcement" else if [type] = "L" then "Limitation" else if [type] = "H" then "Holiday" else if [type] = "E" then "Event" else null),
#"Added Custom1" = Table.AddColumn(#"Added Conditional Column", "ET S Time", each [start_time]-#duration(0,5,0,0)),
Custom1 = Table.AddColumn(#"Added Custom1", "GMT S Time", each [start_time]-#duration(0,10,0,0)),
Custom2 = Table.AddColumn(Custom1, "GMT E Time", each [end_time]-#duration(0,10,0,0)),
Custom3 = Table.AddColumn(Custom2, "SGT E Time", each [end_time]-#duration(0,19,0,0)),
Custom4 = Table.AddColumn(Custom3, "SGT S Time", each [start_time]-#duration(0,19,0,0)),
#"Changed Type2" = Table.TransformColumnTypes(Custom4,{{"ET S Time", type datetime}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type2", "ET E Time", each [end_time]-#duration(0,5,0,0)),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"ET E Time", type datetime}, {"SGT S Time", type datetime}, {"SGT E Time", type datetime}, {"GMT E Time", type datetime}, {"GMT S Time", type datetime}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type3",{"ET S Time", "ET E Time", "GMT S Time", "GMT E Time", "SGT E Time", "SGT S Time"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"news_id", type text}})
in
#"Changed Type1"

 

Only start time and end time are really relevant for what we are trying to do - switch from ET (which is produced in power query) to GMT or SGT using buttons or a slicer (pretty much the same difference)

 

news_idstart_timeend_timetypeinactive_from_timevisibility_indmodified_time
1303271/1/2021 5:00:00 AM2/1/2023 5:00:00 AMA2/1/2023 5:00:00 AMY6/10/2022 10:13:35 AM
1192121/1/2021 5:00:00 AM2/1/2023 5:00:00 AMA2/1/2023 5:00:00 AMY5/23/2022 9:26:56 PM
2711/1/2021 5:00:00 AM1/3/2024 4:45:00 AMA1/3/2024 4:45:00 AMY3/7/2022 6:43:59 PM
1357778/19/2022 10:00:00 PM8/20/2022 10:00:00 PMH8/20/2022 10:00:00 PMY12/24/2021 4:55:57 PM
1359898/20/2022 4:00:00 AM8/20/2022 7:00:00 PML8/20/2022 7:00:00 PMY8/11/2022 5:00:32 AM
1358868/20/2022 11:00:00 AM8/20/2022 5:00:00 PML8/20/2022 5:00:00 PMY8/3/2022 4:58:20 PM
1359968/20/2022 5:00:00 PM8/20/2022 10:00:00 PML8/20/2022 10:00:00 PMY7/29/2022 4:24:46 AM
1357128/26/2022 4:00:00 AM9/2/2022 4:00:00 AML9/2/2022 4:00:00 AMY8/17/2022 1:48:49 PM
1358758/27/2022 1:00:00 AM8/29/2022 1:00:00 AML8/29/2022 1:00:00 AMY5/12/2022 2:05:59 PM
1359908/27/2022 4:00:00 AM8/27/2022 7:00:00 PML8/27/2022 7:00:00 PMY7/12/2022 9:41:11 PM
1359238/27/2022 4:00:00 AM8/29/2022 4:00:00 AME8/29/2022 4:00:00 AMY4/19/2022 3:45:02 PM
1360128/27/2022 12:00:00 PM8/27/2022 4:30:00 PML8/27/2022 4:30:00 PMN8/17/2022 2:12:25 PM
1357698/29/2022 4:00:00 AM8/30/2022 4:00:00 AMH8/30/2022 4:00:00 AMY12/24/2021 11:46:10 AM
1358038/30/2022 6:30:00 PM8/31/2022 6:30:00 PMH8/31/2022 6:30:00 PMY12/30/2021 7:38:45 AM
1359529/2/2022 10:00:00 PM9/4/2022 8:00:00 PML9/4/2022 8:00:00 PMY5/12/2022 2:05:35 PM
1360139/3/2022 3:00:00 AM9/3/2022 6:00:00 AML9/3/2022 6:00:00 AMY8/18/2022 4:11:17 PM
Labels:
Message 1 of 3
513 Views
0
2 REPLIES 2
Anonymous
Not applicable

‎12-20-2022 06:23 PM

Hi @E_K_ ,

How about use a slicer?

Create a table column with GMT and SGT. Like the following:

column1
GMT
SGT

 

Then put the column1 into the slicer.

 

Then unpivot the GMT and SGT columns in table_time. Like the following:

subject time
GMT 2020/12/21 10:19:20
SGT 2020/12/21 02:19:20

 

Then create a measure in the table_time.

measure =
VAR _1 =
    SELECTEDVALUE ( 'table'[column1] )
RETURN
    IF ( _1 = MAX ( table_time[subject] ), MAX ( table_time[time] ), BLANK () )

 

How to Get Your Question Answered Quickly 

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Message 2 of 3
461 Views
0
E_K_
Helper III
Helper III
In response to Anonymous

‎12-21-2022 12:42 AM

Unfortunately this does not work as I am working with start and end times and was to avoid merging columns to ensure by report actually loads! This means that when transponsing, with my full dataset, I end up with over 200 columns in the calculated table (am assuming you meant to use power query, which is what I tried.

 

Unable to share a sample file - here is some of what the data looks like and a santised query: 

let
Source = Excel.Workbook(File.Contents("H:\Calendar Export 081922.xlsx"), null, true),
#"Grid Results_Sheet" = Source{[Item="Grid Results",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Grid Results_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"news_id", Int64.Type}, {"start_time", type datetime}, {"end_time", type datetime}, {"type", type text}, {"inactive_from_time", type datetime}, {"visibility_ind", type text}, {"modified_time", type datetime}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Type", each if [type] = "A" then "Announcement" else if [type] = "L" then "Limitation" else if [type] = "H" then "Holiday" else if [type] = "E" then "Event" else null),
#"Added Custom1" = Table.AddColumn(#"Added Conditional Column", "ET S Time", each [start_time]-#duration(0,5,0,0)),
Custom1 = Table.AddColumn(#"Added Custom1", "GMT S Time", each [start_time]-#duration(0,10,0,0)),
Custom2 = Table.AddColumn(Custom1, "GMT E Time", each [end_time]-#duration(0,10,0,0)),
Custom3 = Table.AddColumn(Custom2, "SGT E Time", each [end_time]-#duration(0,19,0,0)),
Custom4 = Table.AddColumn(Custom3, "SGT S Time", each [start_time]-#duration(0,19,0,0)),
#"Changed Type2" = Table.TransformColumnTypes(Custom4,{{"ET S Time", type datetime}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type2", "ET E Time", each [end_time]-#duration(0,5,0,0)),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"ET E Time", type datetime}, {"SGT S Time", type datetime}, {"SGT E Time", type datetime}, {"GMT E Time", type datetime}, {"GMT S Time", type datetime}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type3",{"ET S Time", "ET E Time", "GMT S Time", "GMT E Time", "SGT E Time", "SGT S Time"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"news_id", type text}})
in
#"Changed Type1"

 

Only start time and end time are really relevant for what we are trying to do - switch from ET (which is produced in power query) to GMT or SGT using buttons or a slicer (pretty much the same difference)

 

news_idstart_timeend_timetypeinactive_from_timevisibility_indmodified_time
1303271/1/2021 5:00:00 AM2/1/2023 5:00:00 AMA2/1/2023 5:00:00 AMY6/10/2022 10:13:35 AM
1192121/1/2021 5:00:00 AM2/1/2023 5:00:00 AMA2/1/2023 5:00:00 AMY5/23/2022 9:26:56 PM
2711/1/2021 5:00:00 AM1/3/2024 4:45:00 AMA1/3/2024 4:45:00 AMY3/7/2022 6:43:59 PM
1357778/19/2022 10:00:00 PM8/20/2022 10:00:00 PMH8/20/2022 10:00:00 PMY12/24/2021 4:55:57 PM
1359898/20/2022 4:00:00 AM8/20/2022 7:00:00 PML8/20/2022 7:00:00 PMY8/11/2022 5:00:32 AM
1358868/20/2022 11:00:00 AM8/20/2022 5:00:00 PML8/20/2022 5:00:00 PMY8/3/2022 4:58:20 PM
1359968/20/2022 5:00:00 PM8/20/2022 10:00:00 PML8/20/2022 10:00:00 PMY7/29/2022 4:24:46 AM
1357128/26/2022 4:00:00 AM9/2/2022 4:00:00 AML9/2/2022 4:00:00 AMY8/17/2022 1:48:49 PM
1358758/27/2022 1:00:00 AM8/29/2022 1:00:00 AML8/29/2022 1:00:00 AMY5/12/2022 2:05:59 PM
1359908/27/2022 4:00:00 AM8/27/2022 7:00:00 PML8/27/2022 7:00:00 PMY7/12/2022 9:41:11 PM
1359238/27/2022 4:00:00 AM8/29/2022 4:00:00 AME8/29/2022 4:00:00 AMY4/19/2022 3:45:02 PM
1360128/27/2022 12:00:00 PM8/27/2022 4:30:00 PML8/27/2022 4:30:00 PMN8/17/2022 2:12:25 PM
1357698/29/2022 4:00:00 AM8/30/2022 4:00:00 AMH8/30/2022 4:00:00 AMY12/24/2021 11:46:10 AM
1358038/30/2022 6:30:00 PM8/31/2022 6:30:00 PMH8/31/2022 6:30:00 PMY12/30/2021 7:38:45 AM
1359529/2/2022 10:00:00 PM9/4/2022 8:00:00 PML9/4/2022 8:00:00 PMY5/12/2022 2:05:35 PM
1360139/3/2022 3:00:00 AM9/3/2022 6:00:00 AML9/3/2022 6:00:00 AMY8/18/2022 4:11:17 PM
Message 3 of 3
447 Views
0

Helpful resources

Announcements
Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Solution Authors
View All