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

Hi Anonymous 

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.