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
PBILover's avatar
PBILover
Helper V
6 years ago

Hi did you got any solution on this? can you please let me know , i am facing similar situation at my end.

  • BHinote's avatar
    BHinote
    Frequent Visitor
    6 years ago

    Unfortunately No.  I was forced to create separate reports for the various timezones needed, as there does not appear to be a solution for this.

     

    I do not know why the Power BI Team has not addressed this issue, as everything I have found converts the UTC Time and Zone based on the Servers Timezone and not the User's Timezone.  Considering that most National and/or International Businesses will have users and/or clients that work from timezones other than where the data is served it does not make sense that there would not be a solution to address this issue.  Maybe I am wrong, but at a minimum couldn't they just provide an option in the User's Profile that allows the user to set the Timezone they work from and then allow the Date/Times to be converted base on that...   I do not know, but this is a major problem..