Apply TimeZone offset from offset table to datetime table with snowflake schema

[Don-Bot]

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

[Greg_Deckler]

@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.

[Don-Bot]

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

TimezoneOffset test =

 LOOKUPVALUE(

    'TimeZone Offset'[Offset],

    'TimeZone Offset'[TimeZone],

    MAX('One Call Center'[TimeZone])

)

[Greg_Deckler]

@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.

[Don-Bot]

@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: