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
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
GilbertQ's avatar
GilbertQ
Super User
6 years ago
Hi there

You are indeed correct, I was thinking of only displaying a single DateTime value and not for all of the reports.

I currently think unless you had to create the seperate columns and try and manage it that way it would be rather a challenge.

What I would do for my end users is that I would explain that the DateTime of the data is where the data is born. That will ensure that the data is always valid on the time based on where it came from.