I have table with a column ‘data_granularity’ which has values ‘grain1’ and ‘grain2’. My Quicksight dashboard has a filter/parameter “Filter1” and I want to achieve a functionality
if a user selected 1 or more values of Filter1, then query sent to my Redshift cluster should look like below
select * from table where data_granularity = 'grain2'
If there is no value selected in Filter1 then the query should look like
select * from table where data_granularity = 'grain1'
I can’t use Passing Parameter functionality because I want my dataset to be in SPICE for faster dashboard performance. I don’t want to use LIVE datasource (connected to my Redshift cluster) since it will slow down the queries drastically and will not meet my customer’s data retrieval requirements.
Hello @NeerajGautam, I will provide what I believe the most likely solution to this issue is, but if you still need assistance afterwards, please post a new topic in the community and link to this question to provide relevant information. That will make sure you are at the top of the priority list for a response from one of our QuickSight experts.
As for the problem you are facing, you are not going to be able to edit the query to redshift based on a user selection, but rather filter the dataset in the analysis since SPICE is storing a snapshot of the data. If you want a filter to apply differently when all options are selected, we can manage that in the analysis.
You should utilize a multi value parameter, set the static default to ALL_VALUES so select all is the default, then link the control to the column in your dataset that you want the user to filter by. Then, you can handle the initial default filter option in a calculated field.
Using this calculation instead of applying the filter directly to the visual, will allow you to customize how the filter is returning values in your dataset. Using the logic of in(NULL, ${Parameter}), checks if select all is displayed. Using this logic, you should be able to get to the desired result, but if you still need assistance we can follow-up in a new topic. Thank you!