Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 -

countryratedate
MX.503/10/2020
MX.803/11/2020
US.703/10/2020
US.903/11/2020
MX.803/20/2020
US.703/20/2020

 

Table 2

 

Date
03/11/2020

 

Result Table -

 

countryratedate
US.903/11/2020
MX.803/11/2020

 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    NaturalinnerJoin on date column will work for you.

     

    Or you can simply create one column in first table.

     

    Column=Lookupvalue(table2[date],table2[Date],table1[date])

     

    Filter out this column to is not blank.

     

    Thanks & regards,
    Pravin Wattamwar
    www.linkedin.com/in/pravin-p-wattamwar

    If I resolve your problem Mark it as a solution and give kudos.

     

     

     

  • technolog's avatar
    technolog
    Super User
    10 months ago

    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.