Forum Discussion
Calculated Table
Hi, We have 2 tables and the requirement is we have to compare date from table 2 with date from table 1 and provide output as calculated table. Thank you in advance. Table 1 - country rate ...
Option A. Filter with IN
Result =
FILTER(
Table1,
Table1[date] IN VALUES(Table2[Date])
)
This semijoins Table1 by the distinct dates in Table2. Works even if Table2 has many dates. No relationship needed.
Option B. Natural inner join by aligning column names
Result =
NATURALINNERJOIN(
Table1,
DISTINCT(
SELECTCOLUMNS(Table2, "date", Table2[Date])
)
)
SELECTCOLUMNS gives Table2 a column named date so the natural join can match it to Table1[date]. DISTINCT avoids duplicates from repeated dates in Table2.
Option C. Use TREATAS inside CALCULATETABLE
Result =
CALCULATETABLE(
Table1,
TREATAS(VALUES(Table2[Date]), Table1[date])
)
TREATAS applies the Table2 dates as a filter over Table1. It is very readable in larger models.