Anti Join

Hi Anonymous ,

By my test , you can create a calculated  table using EXCEPT function to implement left anti join and use the following formula to implement inner join. In  my sample, the table1 is your table A.  the  table inner join is your table B.  And the table left anti join is your table C.

1. Create the calculated table “left anti join” and “inner join”

left anti join = EXCEPT(Table1,Table2)

inner join =

VAR a =

    ADDCOLUMNS (

        CALCULATETABLE (

            Table1,

            FILTER ( Table1, 'Table1'[ Date] IN VALUES ( 'Table2'[ Date] )  )

        ),

        "datea", [ Date]

    )

VAR b =

    ADDCOLUMNS (

        a,

        "table2.spent", LOOKUPVALUE ( Table2[spent], Table2[ Date], [datea] ),

        "table2.id", LOOKUPVALUE ( Table2[id], Table2[ Date], [datea] )

    )

RETURN

    b

1. Manage the relationships with table “inner join” and “left anti join”. Choose Cardinality to  many to one.

1. Then get the results.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.