This post was inspired by @saud968 from the Power BI Community and their post on Multiple values from same column under same visual
Their question centered around being able to find all the survey results that were equal to 4 AND all the survey results that were equal to 5. While the wording I have used here, specifically the word AND, is commonly how we approach and think of the problem, it will unfortunately trip you up when working with advanced filter logic.
I am going to keep this post generic, as this concept can apply to DAX Logical Operators (specifically && and ||), Excel Advanced Filter conditions, Power Query Advanced Filters with And / Or selection and more!
The Problem
Let's use the below table as our sample data. Nice and simple, only 5 rows of ice cream orders:
| Order ID | Name | Location | Country | Flavor | Scoops |
|---|---|---|---|---|---|
| 1 | Allison | Chicago | USA | Chocolate | 2 |
| 2 | Phil | Wellington | NZ | Vanilla | 2 |
| 3 | Allison | Auckland | NZ | Chocolate | 1 |
| 4 | Vijay | New York | USA | Mint Chocolate Chip | 1 |
| 5 | Agnes | San Francisco | USA | Vanilla | 1 |
The boss wants to know the total number of scoops for Chocolate of any kind, so Chocolate and Mint Chocolate Chip scoops combined total. How do we write the filter - using AND vs OR?
Let's start simpler:
If we want all Orders for vanilla that's easy: Order IDs 2 and 5.
If we want all Orders for 1 scoop that's easy: Order IDs 3, 4 and 5.
But what if we want Orders for vanilla AND 1 scoop?
What about Orders for vanilla OR 1 scoop?
AND: 1 scoop AND Vanilla
Let's start with the AND condition:
As you can see in the Venn Diagram above, I have moved Order ID 5 to the middle of the Venn Diagram, since it is both a Vanilla AND a 1 scoop order, so it falls into BOTH filter conditions.
If we choose an AND filter, we will only get the intersection of the chosen conditions, in this case, Order ID 5 only.
OR: 1 scoop OR Vanilla
But what if we wanted all four of those Orders 2, 3, 4 and 5?
Well, again referring to the Venn Diagram above, in order to get all orders, we need to include ALL orders that fall into Vanilla Orders and ALL orders that fall into 1 Scoop Orders.
As I have mentioned before, the use of that pesky little word and in the sentence above often gets us into trouble. We have already demonstrated that when using the AND filter, we only get Order ID 5. So to get all four orders we must need to use the OR condition. But why?
Let's rephrase our question to look at EACH order individually, as that is how the filter conditions are evaluated when we use them in the various Power BI, Excel, etc applications:
Does each Order have either Vanilla OR 1 scoop?
Now we have the key to success. Looking at EACH item individually:
- Order ID 1, does it have either vanilla or 1 scoop? NO
- Order ID 2, does it have either vanilla or 1 scoop? YES, vanilla only
- Order ID 3, does it have either vanilla or 1 scoop? YES, 1 scoop only
- Order ID 4, does it have either vanilla or 1 scoop? YES, 1 scoop only
- Order ID 5, does it have either vanilla or 1 scoop? YES, vanilla AND 1 scoop
So now we can see that by using the OR condition on EACH item, we get the desired result of all four orders that have either 1 scoop or vanilla.
EACH item is evaluated individually
Now that we know that each item is evaluated individually, let's rephrase our questions and revisit the AND filter.
Does each Order have Vanilla AND 1 scoop?
- Order ID 1, does it have vanilla and 1 scoop? NO
- Order ID 2, does it have vanilla and 1 scoop? NO, vanilla only
- Order ID 3, does it have vanilla and 1 scoop? NO, 1 scoop only
- Order ID 4, does it have vanilla and 1 scoop? NO, 1 scoop only
- Order ID 5, does it have vanilla and 1 scoop? YES, vanilla AND 1 scoop
Find the Chocolate and Mint Chocolate Chip combined total scoops
To determine which filter condition (AND vs OR) to use, we need to think about evaluating EACH item individually.
If we draw our Venn Diagram, we can see that nothing is in the middle AND condition section:
Does each Order have Chocolate AND Mint Chocolate Chip?
- Order ID 1, does it have Chocolate and Mint Chocolate Chip? NO, Chocolate only
- Order ID 2, does it have Chocolate and Mint Chocolate Chip? NO
- Order ID 3, does it have Chocolate and Mint Chocolate Chip? NO, Chocolate only
- Order ID 4, does it have Chocolate and Mint Chocolate Chip? NO, Mint Chocolate Chip only
- Order ID 5, does it have Chocolate and Mint Chocolate Chip? NO,
Does each Order have Chocolate OR Mint Chocolate Chip?
- Order ID 1, does it have Chocolate or Mint Chocolate Chip? YES, Chocolate only
- Order ID 2, does it have Chocolate or Mint Chocolate Chip? NO
- Order ID 3, does it have Chocolate or Mint Chocolate Chip? YES, Chocolate only
- Order ID 4, does it have Chocolate or Mint Chocolate Chip? YES, Mint Chocolate Chip only
- Order ID 5, does it have Chocolate or Mint Chocolate Chip? NO,
Therefore, in order to get both Chocolate AND Mint Chocolate Chip orders, we actually need to use an OR filter condition.
Solution
In conclusion, be careful how you word your filter condition questions - don't mislead yourself. In order to pick the correct filter condition (AND vs OR):
- Evaluate EACH item individually
- Make a Venn Diagram
- AND filter condition: Intersection of all conditions (aka the middle of the Venn diagram)
- OR filter condition: Union of all conditions (aka everything within the Venn diagram)
Now we can confidently tell the boss that there are 4 total scoops that were either Chocolate OR Mint Chocolate Chip because we used an OR filter condition to filter our data.
