How do I search multiple drop down lists on a BI Dashboard when it has different entries?
11/10/2018

Unfortunately, is not possible to type into a drop-down list in BI Dashboards in order to filter/narrow down the list of entries.

However, the solution we can implement here is a search facility.

Firstly we need to add a text box to the Dashboard and give this Dynamic Visibility. Please note Dynamic Visibility is only required if you want the search box only to be displayed in unique circumstances.

Some of these might be when there are multiple combo boxes or tables contained within the dashboard.

Link the text box to a cell in the spreadsheet as this will be used later on in the process.
We then need to create or amend the Universe query that returns the drop-down list items in order to take a prompted value.
When amending the query ensure that the prompt is set to match a pattern otherwise the query is likely not to return any rows.
The next step in the process is to set a query trigger cell so that when we search the query is fired and the reduced list gets displayed.
Use a formula on the trigger cell to ensure that a {82d3856af180cc9d2fc773d417a6c0834e49192060d368503f5ba20bb6995f1b} is added before and after the search text this will ensure that all rows with that pattern included will be returned.

This also ensures that the value will change upon each letter being entered and fire the query.
The formula should be based on the cell the search box is linked to.
Once you have typed some text wait a couple of seconds for the query to finish running and then select the arrow on the drop-down box. It will now show a shorter list and enable you to select an entry a lot quicker and easier.
To make use of the same search text box across multiple drop-down lists we need to retrigger each query that populates the drop-down list box at the right time so that multiple queries are not run at the same time unnecessarily.
To do this we need to have a cell that changes when a particular drop down box is shown/selected that will identify which query to retrigger.
This is where dynamic visibility comes in and can be of good use as the status of a cell changes when the drop-down is selected.

When this changes we can then take the input of the search box and using the formula populate a different trigger cell for each drop-down list.

The final thing we need to do from a usability perspective, in this case, would be to clear the text box when switching between drop-down lists.

This is done simply by having another formula so that when a cell value changes the cell the search box is linked to is set to a NULL value therefore clearing the box and returning a full result set.

By using keyintelligence.uk, you agree to our use of cookies to enhance your experience.