Power BI is a powerful tool for data visualization and business intelligence, but handling timezones can be tricky. This guide will walk you through two different methods to implement timezone functionality in your Power BI reports, allowing you to dynamically adjust timezones using a dropdown menu.
Method 1: Using Direct Query and Parameters
Step 1: Create a Parameter
1. Open Power BI Query Editor.
2. Click on ‘New Parameter.’
3. Add details for the parameter in the form, specifying the timezones you need.
Step 2: Convert Queries to Direct Query Mode
1. Convert your data tables from import method to direct query method.
2. Add the timezone parameter to adjust the time in the database. Use the following query as a template:
Here’s my query to get Timein according to timezone parameter value:
Query = Text.Combine({
“SELECT UserID, FirstName, LastName, Email, ParentShiftID, AttendanceID, TimeIn, TimeOut, Timezone, RecordEntryTimezone, ShiftSecs, ShiftMins, BreakSecs, BreakMins, ChargeableSecs, ChargeableMins, ProductiveSecs, ProductiveMins, IdleMinsDistributed, IdleSecsFormula, IdleMinsFormula, TimesheetStatus, TimesheetStatusDesc, AdjustmentStatus, AdjustmentStatusDesc, ApprovedBy, ApprovedAt, DATEADD(minute, “, Number.ToText(Timezone*60), “, TimeIn) AS TimeInAdjusted FROM AttendanceListingView”
})
Step 3: Create a Timezone Table
1. Open Power BI Desktop and go to the Model View.
2. Click on ‘New Table’ and enter a query to create a table containing all timezones. as shown in pic, I’ve added 5 timezones (9.5 to 11.5), but you can add as much as you want, but make sure this new table query contains all timezones that you’ve added in the previous timezone parameter (Step 1):
Step 4: Bind the Parameter
1. Click on the Timezone column in the new table.
2. In the properties pane, bind the parameter to the timezone parameter created earlier.
Step 5: Add a Slicer
1. Add a slicer to your report.
2. Use the newly created Timezone column in this slicer.
Remember to test thoroughly to ensure the dashboard behaves as expected when switching timezones.
