Forum Discussion
Convert Timezone based on User Selection (PowerBI.com)
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.
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- 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. Hi did you got any solution on this? can you please let me know , i am facing similar situation at my end.
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..
