Forum Discussion

BHinote's avatar
BHinote
Frequent Visitor
6 years ago

Convert Timezone based on User Selection (PowerBI.com)

I have been using Power BI for a couple of months now and have run into the same issue as many other people, regarding the issue of showing Date Time fields relative to the end user's Time Zone.  I h...
Need Help
GilbertQ's avatar
GilbertQ
Super User
6 years ago
Hi there

What I would do, is to create a disconnected table which would have the different hours for the different time zones.

I would then take the value being selected from the user and then this would change the measure for the time for the user.

This would then reflect the time in their time zone. Along with the persistent filters, every time the user logged in it would remember and show them the data in their timezone.
BHinote's avatar
BHinote
Frequent Visitor
6 years ago

Thank you GilbertQ for responding to my post.

 

I understand the principle of creating a Disconnected Table that would contain the various Timezones available for the user to Select and then I would use a Slicer based on the Disconnected Table allowing the user to make a selection.  I then assume that I would need to create a New Measure using the SelectedValue() and Switch() functions to determine what value was selected in the Slicer.

 

However, what I do not understand is how I can use a Measure to convert the Date/Time fields on a record by record bases.   To my knowledge Measures expect to generate totals across multiple records at the time the Visual is being loaded, whereas Calculated Columns perform calculations at the Record Level but only during the data refresh which occurs prior to the visuals being loaded or the user making a selection.

 

At the Dax Level I can create a Calculated Column for every timezone supported using code similar to the following:

DateTimeField_PST = MyTable[DateTimeField]  + ((1/24)*-8)      (This is for Pacific Standard Time)
The issue with this is that I would have to have Calculated Columns for every possible timezone I was supporting(i.e. 8 of them currently), but even then I am not aware of a way to use the Switch() function to consume the 8 possible columns, as the Switch function appears to be expecting to use a measure field or to perform measure functionality across multiple records.
 
Am I missing something that would allow me to create a measure that could do something like the following: (The below example is a Calculated Column, which does not recalculate when the user makes a selection but I do not know of a way to do the same thing as a measure or achieve the same result using a measure.)
DateTimeField_LocalTime =
     VAR ValueSelected = SELECTEDVALUE('Time Zones'[Time Zone], "PST")
     VAR Offset =
          CALCULATE (
          FIRSTNONBLANK ( 'Time Zones'[Offset], TRUE() ),
               FILTER ( ALL ( 'Time Zones' ), 'Time Zones'[Time Zone] = ValueSelected)
          )
RETURN
     MyTable[DateTimeField] + ((1/24)*Offset)
Thank you