Select Different Granularity Data From Same Table Based on Filter Value

Question & Answer
, , ,
1

Hi,

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'
2

Hi @NeerajGautam

Are you trying to apply user selected filters along with data granularity filter … like below .

When #Filter_Values >=1
select * from table where data_granularity = ‘grain2’ and filter_column in ( filter_values)

When #Filter_Values =0
select * from table where data_granularity = ‘grain1’ and filter_column is null

If you could provide actual usecase it will be more helpful to get the context .

Thanks
VInod

3

Hi @apjvinod

Yes something similar. Below is the actual use case

When #Filter_Values >=1
select * from table where data_granularity = ‘grain2’ and filter_column in ( filter_values);

When #Filter_Values =0
select * from table where data_granularity = ‘grain1’;

1 Like
4

Hi @NeerajGautam

Have you looking into the new passing parameters to a dataset?

image
image719×408 15.4 KB

5

Hi,

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.

6

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.

ifelse(
in(NULL, ${Parameter}) and {data_granularity} = 'grain1', {data_granularity},
in({data_granularity}, ${Parameter}), {data_granularity},
NULL
)

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!