Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
For creating a new table.
Table3 =
FILTER ( Table1, NOT ( Table1[WorkplaceID] IN VALUES ( Table2[WorkplaceID] ) ) )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
For creating a new table.
Table3 =
FILTER ( Table1, NOT ( Table1[WorkplaceID] IN VALUES ( Table2[WorkplaceID] ) ) )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Works like a charm, thanks Jihwan!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
106 | |
87 | |
74 | |
66 |
User | Count |
---|---|
125 | |
114 | |
98 | |
81 | |
73 |