Here is a simple two column dataset which shows EmpID in column A and DateWorked in column B. My objective is to filter the table to show those employees who worked ONLY on August 17 and August 18. I’d like to exclude employees who:
- Did not work on both these days; and
- Worked on both these dates but also worked on other dates
A simple filter on the DatesWorked column with the criteria of August 17 and August 18, would return 7 employees. This is clearly wrong because this will show employees who:
- Worked on any of the two chosen dates; and
- Worked on dates other than the two chosen dates
From Table 1, one can clearly observe that the correct result should be EmpID E. Only EmpID E worked on August 17 and 18. While others also worked on both these dates, they worked on other dates as well and should therefore be excluded.
The problem outlined above holds true for selections made via a slicer in a Pivot Table as well. So this is a generic problem with a filter/slicer – conditions specified therein behave like OR conditions, not like AND conditions.
Base dataset
| EmpID | DateWorked |
| A | 14-08-2017 |
| A | 15-08-2017 |
| A | 16-08-2017 |
| A | 17-08-2017 |
| A | 18-08-2017 |
| B | 15-08-2017 |
| B | 16-08-2017 |
| B | 18-08-2017 |
| C | 14-08-2017 |
| C | 15-08-2017 |
| C | 16-08-2017 |
| C | 17-08-2017 |
| C | 18-08-2017 |
| D | 14-08-2017 |
| D | 15-08-2017 |
| D | 16-08-2017 |
| D | 17-08-2017 |
| D | 18-08-2017 |
| E | 17-08-2017 |
| E | 18-08-2017 |
| F | 16-08-2017 |
| F | 17-08-2017 |
| G | 18-08-2017 |
Filtered dataset (after specifying the criteria as 17 Aug and 18 Aug)
| EmpID | DateWorked |
| A | 17-08-2017 |
| A | 18-08-2017 |
| B | 18-08-2017 |
| C | 17-08-2017 |
| C | 18-08-2017 |
| D | 17-08-2017 |
| D | 18-08-2017 |
| E | 17-08-2017 |
| E | 18-08-2017 |
| F | 17-08-2017 |
| G | 18-08-2017 |
So the question is “How does one change the behavior of a filter/slicer from OR to AND?”. To make things worse, the data is arranged as follows:
| EmpID | DateWorked |
| A | 14/8/17;15/8/17;16/8/17;17/8/17;18/8/17 |
| B | 15/8/17;16/8/17;18/8/17 |
| C | 14/8/17;15/8/17;16/8/17;17/8/17;18/8/17 |
| D | 14/8/17;15/8/17;16/8/17;17/8/17;18/8/17 |
| E | 17/8/17;18/8/17 |
| F | 16/8/17;17/8/17 |
| G | 18-08-17 |
As you can see, there are multiple dates in a single cell separated by a ;.
You may refer to my Power Query and PowerPivot solution here.
Alter the behaviour of a filter/slicer from OR to AND
{ 3 Comments }