I have a Snowflake Schema setup where I have a main Ticket table which is connected to a DateTime table. Then in order for me to see what timezone the records are in I need to connect to a Call Center Table which is then connected to the TimeZone offset table. How can I set this up so that I can apply the appropriate timezone offset to the datetime table?
I've tried creating calculated columns that work with these but everything I try fails.
EDIT:
I have tried different methods to achieve this with some success in finding a work around but none in auto assigning a timezone. Below is an update I put as a reply in this post. I've modified my original ask as well.
I am working with a Apps Own Data embedded setup for Power BI. The semantic model is an import model coming from databricks.
The model is also now setup as a star schema.
Below is some fake data. The data comes into our system as EST. I would like to be able to pass the timezone offset from javascript via embedded and auto setup the timezone the user sees.
I have tried the following measures with no success (as they keep timing out). A user may create a table visual with thousands of rows in it with their datetimes laid out in it.
I hardcoded the -3 below just to try to convert to PST from EST.
AdjustedCreateDateTime =
CALCULATE(
MAX('CreateDateTime'[CreateDateTime]) + -3/ 24
)
Fact Table
| TicketID | CreateDateTimeID | ItemCount |
| a | 220554 | 1 |
| b | 220746 | 1 |
| c | 220747 | 1 |
| d | 220748 | 1 |
| e | 220749 | 1 |
| f | 220750 | 1 |
| g | 220751 | 1 |
| h | 220752 | 1 |
| i | 220753 | 1 |
| j | 220754 | 1 |
| k | 220755 | 1 |
| l | 220756 | 1 |
| , | 220757 | 1 |
| m | 220758 | 1 |
| o | 220759 | 1 |
| p | 220760 | 1 |
| q | 220761 | 1 |
| r | 220762 | 1 |
| s | 220763 | 1 |
| tt | 220756 | 1 |
| u | 220757 | 1 |
| v | 220758 | 1 |
| ww | 220759 | 1 |
| sx | 220760 | 1 |
| df | 220761 | 1 |
| sdf | 220762 | 1 |
| sdfa | 220763 | 1 |
CreateDateTime
| DateTimeID | DateID | DateTIME |
| 221023 | 9254 | 1/2/2024 13:00 |
| 220746 | 9237 | 1/1/2024 0:00 |
| 220747 | 9237 | 1/1/2024 1:00 |
| 220748 | 9237 | 1/1/2024 2:00 |
| 220749 | 9237 | 1/1/2024 3:00 |
| 220750 | 9237 | 1/1/2024 4:00 |
| 220751 | 9237 | 1/1/2024 5:00 |
| 220752 | 9237 | 1/1/2024 6:00 |
| 220753 | 9237 | 1/1/2024 7:00 |
| 220754 | 9237 | 1/1/2024 8:00 |
| 220755 | 9237 | 1/1/2024 9:00 |
| 220756 | 9237 | 1/1/2024 10:00 |
| 220757 | 9237 | 1/1/2024 11:00 |
| 220758 | 9237 | 1/1/2024 12:00 |
| 220759 | 9237 | 1/1/2024 13:00 |
| 220760 | 9237 | 1/1/2024 14:00 |
| 220761 | 9237 | 1/1/2024 15:00 |
| 220762 | 9237 | 1/1/2024 16:00 |
| 220763 | 9237 | 1/1/2024 17:00 |
| 220764 | 9237 | 1/1/2024 18:00 |
| 220765 | 9237 | 1/1/2024 19:00 |
| 220766 | 9237 | 1/1/2024 20:00 |
| 220767 | 9237 | 1/1/2024 21:00 |
| 220768 | 9237 | 1/1/2024 22:00 |
@Don-Bot Well, you can either make your Tickets to Call Center and Call Center to Timezone Offset relationships Both directions or you can use LOOKUPVALUE or MAXX( FILTER( ... ), ... ) to lookup your values. Lookup the Call Center from and then lookup the Timezone offset.
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
Thanks @Greg_Deckler ,
I have tried adding the below LookupValue as a measure in the DateTime table but I get the error The column 'One Call Center[Timezone]' either doesn't exist or doesn't have a relationship to any table available in the current context.
Placing this measure in the CreateDate table gives a Query has exceeded the available resources error as well
Also, I don't care for the "Max" in the query below so will look into how to replace that
@Don-Bot I'm not sure that you can use RELATED since they are not related ( your relationship direction is not the right direction from what I can tell ). Sample data would really help to arrive at a solution here.
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
@Greg_Deckler ,
Let me know if this helps.
I am working with a Apps Own Data embedded setup for Power BI. The semantic model is an import model coming from databricks.
The model is also setup as a star schema.
Below is some fake data. The data comes into our system as EST. I would like to be able to pass the timezone offset from javascript via embedded and auto setup the timezone the user sees.
I have tried the following measures with no success (as they keep timing out). A user may create a table visual with thousands of rows in it with their datetimes laid out in it.
I hardcoded the -3 below just to try to convert to PST from EST.
AdjustedCreateDateTime =
CALCULATE(
MAX('CreateDateTime'[CreateDateTime]) + -3/ 24
)
Fact Table
| TicketID | CreateDateTimeID | ItemCount |
| a | 220554 | 1 |
| b | 220746 | 1 |
| c | 220747 | 1 |
| d | 220748 | 1 |
| e | 220749 | 1 |
| f | 220750 | 1 |
| g | 220751 | 1 |
| h | 220752 | 1 |
| i | 220753 | 1 |
| j | 220754 | 1 |
| k | 220755 | 1 |
| l | 220756 | 1 |
| , | 220757 | 1 |
| m | 220758 | 1 |
| o | 220759 | 1 |
| p | 220760 | 1 |
| q | 220761 | 1 |
| r | 220762 | 1 |
| s | 220763 | 1 |
| tt | 220756 | 1 |
| u | 220757 | 1 |
| v | 220758 | 1 |
| ww | 220759 | 1 |
| sx | 220760 | 1 |
| df | 220761 | 1 |
| sdf | 220762 | 1 |
| sdfa | 220763 | 1 |
CreateDateTime
| DateTimeID | DateID | DateTIME |
| 221023 | 9254 | 1/2/2024 13:00 |
| 220746 | 9237 | 1/1/2024 0:00 |
| 220747 | 9237 | 1/1/2024 1:00 |
| 220748 | 9237 | 1/1/2024 2:00 |
| 220749 | 9237 | 1/1/2024 3:00 |
| 220750 | 9237 | 1/1/2024 4:00 |
| 220751 | 9237 | 1/1/2024 5:00 |
| 220752 | 9237 | 1/1/2024 6:00 |
| 220753 | 9237 | 1/1/2024 7:00 |
| 220754 | 9237 | 1/1/2024 8:00 |
| 220755 | 9237 | 1/1/2024 9:00 |
| 220756 | 9237 | 1/1/2024 10:00 |
| 220757 | 9237 | 1/1/2024 11:00 |
| 220758 | 9237 | 1/1/2024 12:00 |
| 220759 | 9237 | 1/1/2024 13:00 |
| 220760 | 9237 | 1/1/2024 14:00 |
| 220761 | 9237 | 1/1/2024 15:00 |
| 220762 | 9237 | 1/1/2024 16:00 |
| 220763 | 9237 | 1/1/2024 17:00 |
| 220764 | 9237 | 1/1/2024 18:00 |
| 220765 | 9237 | 1/1/2024 19:00 |
| 220766 | 9237 | 1/1/2024 20:00 |
| 220767 | 9237 | 1/1/2024 21:00 |
| 220768 | 9237 | 1/1/2024 22:00 |
I was able to get it to work with a parameter field selector but our preferred method would be an auto assigned timezone. Which all the below is pre-created timezone fields that the user can select.
Working method:
Helpful resources
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.
Fabric Community Update - June 2025
Find out what's new and trending in the Fabric community.
