- Create a parameter called pExclude, set it up as a 'Boolean'
- Create a calculated field with the formula: ([pExclude] = true and [Country] != 'USA') or pExclude=false
- Drag the calculated field onto the filter shelf and set it to true. Now show the parameter control on the sheet (right click the parameter in the bottom left and select show parameter control)
Create a parameter called [Exclude USA] (with "Yes" and "No"), then create a calculated field to use as your Quick Filter — something like the following:
IF [Exclude USA] = 'No' OR [Country] <> 'USA' THEN [Country] END
You may need to duplicate this field to use as two filters: one fixed filter to exclude the resulting NULLs, and the second one to use as the Quick Filter for user interaction.
1 of 1 people found this helpful
Flattered as I am that you selected my answer as the correct one, I want to point out that Tom's answer is actually more elegant, because it does not produce NULLs that have to be cleaned up.
In fact, the calculated field to use on the Filter shelf can be simplified even further:
NOT [pExclude] OR [Country] != 'USA'
My solution was definitely over-engineered. I tend to do that sometimes, especially late in the afternoon.
Absolutely. I am new to the community and did not realize that I could not mark both as correct. I appreciate both of your quick responses. My workbook is now working exactly per the end users requirements!
You can't mark more than one answer correct, which is why I pointed out that Tom's answer may be the more appropriate one to mark. The correct answers gets elevated to the top of the thread, making it easier for other people to find it later. You get to choose which answer gets elevated to the top.
I'm glad your workbook is working to spec now!