Forum Discussion

matthewtjy's avatar
matthewtjy
Helper I
5 years ago
Solved

Create Table By Excluding Rows From Another Table

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

WorkplaceIDChemicalIDTypeIDDate
AHydrochloric acidArea1 Jan 2012
ANitric acidPersonal5 Jan 2012
BHydrochloric acidPersonal2 Jan 2012
BHydrochloric acidPersonal6 Jan 2012
CSulphuric acidArea1 Jan 2012
CPotassium hydroxideArea3 Jan 2012
DMagnesium hydroxideArea4 Jan 2012
EPotassium hydroxidePersonal3 Jan 2012
FSulphuric acidArea5 Jan 2012

 

Table 2

WorkplaceIDChemicalIDTypeIDDate
ANitric acidPersonal5 Jan 2012
BHydrochloric acidPersonal2 Jan 2012
BHydrochloric acidPersonal6 Jan 2012
EPotassium hydroxidePersonal3 Jan 2012

 

[Desired Result] Table3

WorkplaceIDChemicalIDTypeIDDate
CSulphuric acidArea1 Jan 2012
CPotassium hydroxideArea3 Jan 2012
DMagnesium hydroxideArea4 Jan 2012
FSulphuric acidArea5 Jan 2012

 

  • For creating a new table.

     

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

2 Replies

  • For creating a new table.

     

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