Create Table By Excluding Rows From Another Table

[matthewtjy]

Hi, I have currently two tables, Table1 and Table2.

Table1 is the main data set;
Table2 is derived from Table1 using filters to only include rows where TypeID="Personal"

i.e. Table2 =
FILTER(

'Table1',

'Table1'[TypeID]="Personal"
)

I would now like to create a new Table3 where it is also derived from Table1 but excludes rows where 'Table1'[WorkplaceID]='Table2'[WorkplaceID].

I have tried using the following formula but it doesn't work:

Table3 =
FILTER(
'Table1',
'Table1'[WorkplaceID]<>'Table2'[WorkplaceID]
) 

Problem with the above formula is that I am unable to key in the column 'Table2'[WorkplaceID] into the formula.

Table1

WorkplaceID	ChemicalID	TypeID	Date
A	Hydrochloric acid	Area	1 Jan 2012
A	Nitric acid	Personal	5 Jan 2012
B	Hydrochloric acid	Personal	2 Jan 2012
B	Hydrochloric acid	Personal	6 Jan 2012
C	Sulphuric acid	Area	1 Jan 2012
C	Potassium hydroxide	Area	3 Jan 2012
D	Magnesium hydroxide	Area	4 Jan 2012
E	Potassium hydroxide	Personal	3 Jan 2012
F	Sulphuric acid	Area	5 Jan 2012

Table 2

WorkplaceID	ChemicalID	TypeID	Date
A	Nitric acid	Personal	5 Jan 2012
B	Hydrochloric acid	Personal	2 Jan 2012
B	Hydrochloric acid	Personal	6 Jan 2012
E	Potassium hydroxide	Personal	3 Jan 2012

[Desired Result] Table3

WorkplaceID	ChemicalID	TypeID	Date
C	Sulphuric acid	Area	1 Jan 2012
C	Potassium hydroxide	Area	3 Jan 2012
D	Magnesium hydroxide	Area	4 Jan 2012
F	Sulphuric acid	Area	5 Jan 2012

[Jihwan_Kim · Accepted solution]

For creating a new table.

Table3 =
FILTER ( Table1, NOT ( Table1[WorkplaceID] IN VALUES ( Table2[WorkplaceID] ) ) )

[matthewtjy]

Works like a charm, thanks Jihwan!