I’m creating a dashboard that will return data based on a user selection of two criteria, in this case the agencies that made a referral will be returned based on the quarter and program they referred into.
The tricky thing is, I wish the user to be able to select both or either of the criteria to search. That is, a user should have the choice to see all agencies who referred in 2023-Q1 for Program A, or all agencies that referred in 2023-Q1, or all all agencies who have referred to Program A. As there may be multiple agencies per period/program, I used the FILTER function rather than V or XLOOKUP.
Before posting what I tried, let me show the final variant which I got to work:
=IF(AND($F$2<>””,$G$2<>””),(FILTER(Table4[Agency],(Table4[Session]=Dashboard!$F$2)*(Table4[Program]=Dashboard!$G$2))),IF($F$2<>””,(FILTER(Table4[Agency],(Table4[Session]=Dashboard!$F$2))),
IF($G$2<>””,
(FILTER(Table4[Agency],(Table4[Program]=Dashboard!$G$2))))))
My data is stored in a table for expandability, and there are many other fields which are referenced elsewhere.
I started with
=FILTER(Table4[Agency],(Table4[Session]=Dashboard!$F$2)*(Table4[Program]=Dashboard!$G$2))
with AND logic to produce a list when both search terms were present.
To add in the OR logic, I thought I could do something like:
=IFERROR((FILTER(Table4[Agency],(Table4[Session]=Dashboard!$F$2)*(Table4[Program]=Dashboard!$G$2))),
(FILTER(Table4[Agency],(Table4[Session]=Dashboard!$F$2)+(Table4[Program]=Dashboard!$G$2)))
to first try filtering for both terms, then do an OR search if both terms weren’t present; however, getting the either/or type search to work was challenging and resulted in a large number of results being returned, where it seemed that it was matching the blank search term against blank lines of my table.
My solution works, but it seems kind of janky, and I am concerned about the impact on future scalability, and so I’m wondering if anyone has a better solution.
