CODEWITHSUNDEEP
filterpowerbidax
Jake Handy
Jake Handy

Reputation: 11

DAX reverse filter

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

Upvotes: 0

Views: 3722

Answers (2)

Przemyslaw Remin
Przemyslaw Remin

Reputation: 6940

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

Upvotes: 0

Foxan Ng
Foxan Ng

Reputation: 7151

You can try the following DAX:

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

Results:

not in

Upvotes: 2

Related Questions