DAX reverse filter

Question

Working in Power BI -- pretty straight forward, looking to do the exact opposite of the formula below:

newtable = CALCULATETABLE(
                table1, FILTER(
                     table2, table2[ID]
                )
           )

Right now this is filtering 675 of 4423 rows in Table 1 that were found in Table 2. I want it to do the exact opposite: 3748 of 4423 rows in Table 1 NOT found in Table 2.

Cheers

Answer 1

You can try the following DAX:

newtable = 
CALCULATETABLE(
    table1,
    NOT(table1[ID] IN VALUES(table2[ID]))
)

Results:

Answer 2

The simplest way is EXCEPT function:

EXCEPT( BigTable, SmallTable )

This will return a BigTable without elements of SmallTable.

EXCEPT by Alberto:

https://youtu.be/Kk7_TdmxQOI