Forum Discussion
AND vs OR in Advanced Filters
I am using an Advanced Filter on a lookup table with a column called ProductClass. See the screenshot:
Can anyone explain why when I use 'OR', the underlying data doesn't change. Its as if nothing satisfies the filter argument But when I use 'AND', then the filter is applied properly. I would've expected the opposite.
This seems counterintuative based on my coding background. OR statements are typically used to evaluate multiple possible conditions, with only 1 condition needing to be satisfied. Meanwhile AND statements are typically used to evaluate multiple possible conditions, with all conditions needing to be satisfied.
I know for a fact that my underlying data is not satisfying all 4 conditions, since it is a 'does not start with' filter, and a single row value cannot start with 4 different values.
So my questions are:
-am I just misunderstanding 'AND' vs 'OR' filters in PowerBI, or is there something else strange going on?
-Why does the 'AND' filter behave exactly like I would've expected the 'OR' filter to behave based on coding experience, while the 'OR' filter does aboslutely nothing, like I would've expected the 'AND' filter to behave?
I believe I understand now. You're using "Does not equal to," which negates the condition, indicating that you want to exclude certain values. Therefore, Power BI's interpretation is correct.In my example, I utilized "Starts with," which aligned with my intended outcome.
3 Replies
analyticsyc
The way you have understood and explained, yes it is correct. I also tested but it is working correctly. check my screenshot below. Please recheck your result how you got right with AND.Thanks for your response. I agree that your example seems to be working as expected. I've provided additional screenshots showing how it doesn't work for me:
Filter applied with 'OR':
Filter applied with 'AND':
Would you mind testing something for me? If you do 3+ arguments in the advanced filter (first select 3+ items in basic filter, then switch to advanced and it should give you the option for 3+ advanced filters), does the 'OR' operator still work for your data?
I believe I understand now. You're using "Does not equal to," which negates the condition, indicating that you want to exclude certain values. Therefore, Power BI's interpretation is correct.In my example, I utilized "Starts with," which aligned with my intended outcome.
