Quick Edit Filter Chart Complication

[Andrew_Ford2]

Hello Appsheet Community,

I am setting up a reporting page dashboard. The user is able to quick edit a detail view of a filter form that automatically changes the slice of the data. It’s based on this foundation: https://www.appsheet.com/samples/Allow-the-user-to-filter-a-view-based-on-a-form?appGuidString=91266...

For my project, I have 3 columns the user can filter by: Office, District, and HS. I was able to set it up that the tables will automatically filter the combination of what they select in the quick edit detail. To do that, I used this formula in the filter area of the slice:

AND(ANY(reportFilter[Office]) = [Office], ANY(reportFilter[HS]) = [hs], ANY(reportFilter[District]) = [district])

This works. But, I want the user to also be able to not filter by one of these options. To achieve that, I tried adding an “All” option to each filter. I then adjusted the formula in the filter slice to the following:

AND(
IF(NOT([_THISROW].[Office]=“All”), ANY(reportFilter[Office]) = [Office], ISNOTBLANK([Office])),
IF(NOT([_THISROW].[hs]=“All”), ANY(reportFilter[HS]) = [hs], ISNOTBLANK([Office])),
IF(NOT([_THISROW].[district]=“All”), ANY(reportFilter[District]) = [district], ISNOTBLANK([Office]))
)

This does not work . If I have a non-“All” value selected for each filter option the graphs filter correctly. But if I select “All” for any of them then the charts will not show me anything.

[Andrew_Ford2 · Accepted solution]

Figured it out!

AND(
IF(IN(“All”,reportFilter[Office]),ISNOTBLANK([Office]),ANY(reportFilter[Office]) = [Office])
,
IF(IN(“All”,reportFilter[hs]),ISNOTBLANK([Office]),ANY(reportFilter[hs]) = [HS])
,
IF(IN(“All”,reportFilter[district]),ISNOTBLANK([District]),ANY(reportFilter[district]) = [District])
)