Better interaction between custom background filters and exclusive foreground filters

I have a custom background filter and a "notIn"/Exclusion foreground filter.

The background filter does not apply. Please make it apply.

Also, some of my widgets showed an error.

These problems do not apply when the foreground filter is an Inclusion/"in"/"members" filter.

 

1) My scenario

2) Tech support's explanation

3) I believe that implies a problem

4) My suggested change to Sisense

5) A related problem

6) JAQL of my custom filter

 

My scenario:

On my dashboard, I need the "dimWard.Ward Name" dashboard filter to show only items where "dimWard.Ward For CCDM" = 1. So, I created a custom filter on "dimWard.Ward Name" using the JAQL below. Then I set that as a background filter. Now I use the "dimWard.Ward Name" filter as a dashboard user. When I choose to Include All, it works. When I pick a ward, it works. However, when I select all and then exclude a ward, I get an error "Query number 0: SELECT: no such column".

 

Tech support's explanation

When you select "Include All" and then exclude a ward, the dashboard changes your filter into an exclusion filter (notIn). At that point, your original custom background condition (Ward For CCDM = 1) is no longer enforced on that list of wards, and as a result, the filter may include (or exclude) ward values that are outside of the valid CCDM set. 

 

I believe that implies a problem:

Suppose we have [Ward1, CCDM=1], [Ward2, CCDM=1], [Ward3, CCDM=0]. A widget shows [Total Hours For Selected Wards]. A dashboard filter lets the user select wards, but only wards where CCDM=1.

If the user leaves the dashboard filter on "Include All", then [Total Hours For Selected Wards] shows hours from Ward1 and Ward2.

If the user filters to exclude Ward1, then [Total Hours For Selected Wards] shows hours from Ward2 and Ward3. 

That's wrong because the user is expecting Hours to be only for Ward2. It's especially dangerous because unless the Hours are obviously wrong, the user will trust the result without realising that there's a problem.

 

My suggested change to Sisense:

Background filters should always be respected as part of exclusive foreground filters.

 

A related problem:

I got the "no such column" error instead, not the incorrect results. So maybe there's some extra problem. But I'd rather have an error like this than the incorrect values.

 

JAQL of my custom filter:

{

}