# Report > Advanced filtering for exclusions

• Question
• Updated 2 months ago
• (Edited)
Within Report Builder, has anyone solved for a filtering scenario that involves excluding rows based on a combination of fields?

For example, if I have a dataset in which all of my users are assigned a letter (A, B, C, D, etc.) and also a number (1, 2, 3, etc.)

I would like to see all of the users who have an A, and all of the users who have a 1, but I want to exclude from view any of the users who have the combination of A and 1.

Matthew Lind, Champion

• 3,604 Points

Posted 2 months ago

Marcelo, Champion

• 3,412 Points
What if in the filter you set it to Letter != A Number !=1 and then on the filter setting you set it to . (A and B)?

Matthew Lind, Champion

• 3,604 Points
Thanks Marcelo. I've found when I attempt that, I end up excluding all of the A's and then also excluding all of the 1's.

Rakesh, Employee

• 360 Points
Hi Matthew,
Can you try Marcelo's suggestion with (A or B)?
Theoretically, (A ∩ B)' = A' U B'.

For your exact question, filters needed would be
Filter A: Letter A
Filter B: Number 1
Filter C: Not Letter A (!= A)
Filter D: Not Number 1 (!= 1)
In advanced logic: (A or B) and (C or D)
(Edited)

Matthew Lind, Champion

• 3,604 Points
Rakesh, I attempted your suggestion on the dataset and did not get the results I was hoping for. Records I wanted to exclude because they met BOTH of my criteria still appear in the results.

Given that in my use case, I have 4 pairs of values to exclude (I need to exclude A1, A2, A3 and B1), even if this did work, I would end up with at least 16 filters and an advanced logic string that makes me shudder.

The ability to generate a Calculated Field within a Report (in this case, to CONCAT) would give me a more straightforward solution and put Reports at more parity with Bionic Rules.
(Edited)

Rakesh, Employee

• 360 Points
Hi Matthew,
Concat should be one of the functions in the formula fields capability we have on our roadmap!
• 878 Points
In your example, it sounds like there's a "Letter" field that might = A, and a "Number" field that might = 1.  If so, then you'd theoretically just use something like

Filter A:  Letter=A
Filter B: Number=1
and the expression:
(A OR B) NOT (A AND B)

Too bad we can't use NOT as an operator.  I just voted up this related Idea.  Finding another way still sounds like an interesting challenge, but being able to use "NOT" would really tie the room together.  ;)
https://community.gainsight.com/gainsight/topics/filtering-in-reports-for-does-not-start-with

Matthew Lind, Champion

• 3,604 Points
I was guarding against floating a solution to my own question, because I wanted to get some good opinions or creative solutions. You're right, Andy, in that I would use a NOT operator tomorrow if it was available.
• 878 Points
Keep hope alive! :)