Dynamic join with Date Time tables based on timezone slicer in power bi (Oracle SQL)

[VMariapp]

Hi All,

I have a requirement where my Oracle SQL query needs to dynamically join with one of multiple date-time tables (DatetimeEST, DatetimeGMT, DatetimeTYO, DatetimeAET, DatetimeSGP).

The selection should depend on the timezone chosen in a slicer. For example:

If EST is selected → join with DatetimeEST

If GMT is selected → join with DatetimeGMT

If TYO is selected → join with DatetimeTYO

If AET is selected → join with DatetimeAET

If SGP is selected → join with DatetimeSGP

Only one of these 5 tables should be used in the INNER JOIN at a time, depending on the user’s selection.

My challenge is:
How can I make the query in Power BI (Oracle source) dynamically pick the correct date-time table in the join condition based on slicer selection (timezone)?

Has anyone implemented a similar scenario? Any guidance or best approach would be really helpful.

Thanks in advance!

[v-aatheeque]

Hi @VMariapp 

@danextian @rohit1991 have already been shared for your scenario either consolidating the 5 datetime tables into a single table with a timezone column, or handling it within the model/relationships since slicer selections can’t directly swap SQL tables.

Were you able to try either of these solutions for dynamically joining based on timezone selection?

Let us know if one of them worked out for you, or if you still need further assistance.

[VMariapp]

I'm searching more options. Will go for the best approach and try it out. @v-aatheeque 

[v-aatheeque]

Hi @VMariapp 

We haven’t heard from you on the last response and was just checking back to see if your query was answered,

Otherwise, will respond back with the more details and we will try to help .

[v-aatheeque]

Hi @VMariapp 
We’d like to check if you were able to go through the responses to your issue. Please let us know if you need further clarification we’ll do our best to support you.

[danextian]

Hi @VMariapp 

That is currently not possible. Slicer selections and anything from the designer other than changing a query parameter (which is technicaly a part of query) do not get passed on to the query editor. What happens in the query editor affects the designer/semantic model but it isn't the other way around. Your option is to combine all those queries into one with a column that indicates the timezone and use relationships (many-to-many due to the lack of distinct dates, or a one-to-many with a bridge table bi-directional)

[rohit1991]

Hi @VMariapp 

There are three ways to do this :

1) Make one date table with a TZ column (in DB or Power Query).
Create a view in Oracle that stacks the 5 tables and tags each row with the timezone:

SELECT 'EST' AS TZ, d.* FROM DatetimeEST d
UNION ALL
SELECT 'GMT' AS TZ, d.* FROM DatetimeGMT d
UNION ALL
SELECT 'TYO' AS TZ, d.* FROM DatetimeTYO d
UNION ALL
SELECT 'AET' AS TZ, d.* FROM DatetimeAET d
UNION ALL
SELECT 'SGP' AS TZ, d.* FROM DatetimeSGP d;

Join your fact to this single DateTime view on the key, and put TZ on a slicer. Only the selected timezone’s rows will pass through, and folding stays on the server.

2) Keep separate tables, switch the relationship in DAX (no SQL swapping).
Create 5 relationships (only one active; others inactive). Add a small TZ table for the slicer, then in your measure:

Sales (by TZ) =
VAR _tz = SELECTEDVALUE(TZ[Code], "GMT")
RETURN
SWITCH(
  _tz,
  "EST", CALCULATE([Sales], USERELATIONSHIP(Fact[DateKey], DateEST[DateKey])),
  "GMT", CALCULATE([Sales], USERELATIONSHIP(Fact[DateKey], DateGMT[DateKey])),
  "TYO", CALCULATE([Sales], USERELATIONSHIP(Fact[DateKey], DateTYO[DateKey])),
  "AET", CALCULATE([Sales], USERELATIONSHIP(Fact[DateKey], DateAET[DateKey])),
  "SGP", CALCULATE([Sales], USERELATIONSHIP(Fact[DateKey], DateSGP[DateKey]))
)

This keeps one model and lets the measure “pick” the right date table.

3) DirectQuery + Dynamic M parameter (only for filtering).
Bind a slicer to an M parameter (TZ) and reference it in your SQL that queries the unioned view from option 1 (e.g., WHERE TZ = @TZ). This pushes the filter to Oracle, but don’t try to parameterize table names.