Determining When a multi Select Control is blank or unselected

Question

I am working with a dashboard control that is a multi-select dropdown with "Show unfiltered Chart Data" Enabled.

I am working with a visual query that needs to evaluate if the control is 'deselected'. Using a Custom Formula, I've been able to successfully get the following to work as expected.....

CASE WHEN {PRODUCT} != '' THEN 1 END

If I select a single Product in the control the custom formula column will correctly put a 1 on any record with the matching Product. If I select more than one single Product, then I get an error:

[SQLITE_ERROR] SQL error or missing database near (",": syntax error)

I've tried also counting the number of Products selected but couldnt figure that out either.

Jonathan Lowry · Answer

ok, so i did need to find out what the count was for selected items, lol.....

LENGTH("{DROPDOWN}") - LENGTH(REPLACE("{DROPDOWN}", ',', '')) + 1

Jonathan Lowry · Answer

If i may add my experience to this chat, maybe my trial and error outcome might benefit some...

For this to work, your Dropdown control must allow Multi-selection and Show unfiltered Chart data. (i have not tried other configurations, as this setup met what I needed at the time).

AND CASEWHEN ({DROPDOWN}) IS NOT NULL and LENGTH("{DROPDOWN}")>2 THEN   - do something for when there are ANY selected items from the DropdownELSE  - do something for when there are NO selected items from the DropdownEND

If you really needed the count of items in the DROPDOWN, one might be able to take this farther, and Count the commas that would be part of the {DROPDOWN} array value when more than 1 item is selected, and then add 1 to that total.

*Note -  LENGTH("{DROPDOWN}")>2   - the "2" is needed because the Dropdown value(s) are surrounded by single quotations marks, even when nothing is selected, you will get ''

Foster Cryer · Answer

Hey @bryan_n_burrets , please try the formula below and let me know if it helps you flag records that match one of the PRODUCT dropdown selections.

CASE WHEN "Product" IN ({PRODUCT}) THEN 1 ELSE 0 END

Replace "Product" in the formula above with the column name in your chart/query.

Out of curiosity, are you looking to filter with the PRODUCT dropdown, or are you working on a scorecard that counts up how aligned records are to dropdown selections? Or something else?

I have not been able to figure out how to return a count of the number of selected items in a dropdown though, but if you can tell me how you want to use this information, I may be able to figure out a different solution.