Filter table based on another table

[Anonymous]

Hi,

I would like to filter numbers out based on if they exist in another table.

Example given.

Table 1 consist 

ID     Text Field

Table 2 consist 

ID

I'd like to filter Table 1, removing all the rows where the number from Table 2 ID exist.

I've tried by making a new table with Column = EXCEPT(VALUES(Table1[ID]);VALUES(Table2[ID])) 

Which gives me a new table that filters perfectly. however I need to keep my second column "Text Field".

Any ideas?

[Anonymous]

Thanks alot! Works like a charm!

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Use the DAX below instead.

Table = CALCULATETABLE(Table1;EXCEPT(VALUES(Table1[ID]);VALUES(Table2[ID])))

Regards,

Lydia

[SG_17]

@v-yuezhe-msft  I am trying to do something similar, but do not want to build another table.  Instead, I want to calculate measures based on the filter that will be visualized on a matrix in a report. For example:

Table A

ID	Cost	Revenue
11	2	4
12	3	5
13	3	7
14	5	11

Table B (discontinuation)

ID

11

12

Matrix on report:

	Before Disc	After Disc
Total Cost	13	8
Total Revenue	27	18

Any recommendations?  Thank you!!

[Prashant354]

What if I want to keep the values in table 2 and filter out the remaining values from table 1?

[Anonymous]

I believe you can use INTERSECT instead of EXCEPT