How to create a filter field that includes/excludes a specific category associatively?

[priscilarubim]

Hi community,

I would like to know what the best approach would be to create a filter field called [Region] that works as follows:

- Include All Regions - displays all region categories (NA, EU, APAC, LATAM, MEA)

- Exclude APAC - displays all regions except APAC

[RafaelBarrios]

Hi @priscilarubim 

If you're referring to script execution, I would suggest first creating an inline table and then using a join to reduce the table containing the regions. This way, you don't waste time filtering while reading the source or the QVD.

DATA:
load * inline [
Region
NA
EU
LATAM
MEA];

LEFT JOIN (DATA)
LOAD
   Region
   <your other fields>
FROM <database|qvd>;

If you are refering to expressions to limit the results of a table or chart:

sum({< Region-={"APAC"} >} quantity)

This way you can use APAC on some other sheet in the same app.

Hpe this helps.
best,

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 

[priscilarubim]

Hi Rafael, thanks for your response!

What I need is a filter pane with two selectable options that control which categories are visible in the app:

Region Filter:
- > Include All Regions:  NA, EU, APAC, LATAM, MEA 
- > Exclude APAC:  NA, EU, LATAM, MEA 

So when the user selects "Include All Regions", all categories are shown. When they select "Exclude APAC", all categories are shown except APAC.

The challenge is that NA, EU, LATAM and MEA need to belong to both filter options simultaneously.

I am considering using a Bridge Table to handle this, but I am not sure if that is the most elegant approach. 

Thanks!

[RafaelBarrios]

got it,

here a quick example with different data, but im sure you will get the idea

you can do it with two button objects

drag and drop a button and click on "add action"

with one button you can apply selections on a field for one or several values

the other button, will clear the selections on a field, showing everything

hope this helps

Best,

[marksouzacosta]

Hi @priscilarubim,

Do the "bridge" table. You just need to classify those records.

One suggestion is to create a single selection only, like the example below:

Regions:
LOAD 
Region,
If([ExcludeAPAC]=1,'Exclude APAC') AS [Region Filter]
INLINE [
Region, ExcludeAPAC
NA,
NA,1
EU,
EU,1
APAC,
LATAM,
LATAM,1
MEA,
MEA,1
];

This is how it will look in the Dashboard:

Regards,

Mark Costa