[native filters] Applying selected options in parent filter also apply available options in child filter

[jbressand]

Is your feature request related to a problem? Please describe.
Using hierarchical filters, when I select options in parent filter, I see that options are updated in the child filter. But when I click on the apply button, only the parent filter apply. It means that there remains values displayed in charts that do not respect updated values in the child filter. For more explanation, see the gif below.

Describe the solution you'd like
I would like that even if not options are selected in the child filter, applying the parent filter would take into account available options in the child filter.

Describe alternatives you've considered
An alternative would be to force the dashboard user to select values in the child filter. But in some case the user would like to select all available options in the child filter with regard to the parent filter and with a high cardinality.
Another alternative would be to merge different dataset into one, indeed the child filter options would be taken into account indirectly. However I see several drawbacks to it :

• In some case we can't merge all datasets together because of the resulting cardinality
• When merging datasets, you need to choose an order and types of join (left, right, outer, inner) with complexity

Additional context
Here is an exemple.
I have two table charts with two associated datasets :

• Disponibilités : the associated dataset contains two columns, one is a login and the other is called 'dispo' and take values among ('dispo','sous l'eau'). It represents the work availability of coworkers
• Taux horaire : the associated dataset contains two columns, one is a login and the other is called 'prix' and take values among ('cher', 'normal','good'). It represent the cost of coworkers.

In the following gif, I created two hierarchical filters on the 'Disponibilités' dataset :

• The parent filter is on the column 'dispo' and has a scope only on the 'Disponibilités' chart
• The child filter is on the column 'login' and has a scope on both charts.

When I select options in the parent filter, the options in the child filters are updated.
When I click on apply, I see that only the first filter is applied, I would like logins to be filtered too in the chart 'Taux horaire'

I do not see this feature in the following SIP :
I found an issue maybe related to my feature :

[jbressand]

I tried to join my datasets in a view and use that view in my filters, it doesn't work too.

[jbressand]

Steve Marek (TRAC VC) helped me find a workaround with Jinja tempates :

I build a saved query from my second dataset and I display this saved query in a table :
SELECT prix, login from taux_horaire where 1=1 {{ "AND login IN (SELECT login FROM dispos WHERE dispo IN ( '" + "','".join(filter_values('dispo', remove_filter=True)) + "') ) " if filter_values('dispo') is defined and filter_values('dispo') else "" }}

Here is our discussion : https://app.slack.com/client/T7GMC60JX/CCKHMGRRB/thread/CCKHMGRRB-1634639904.159900

I will build a dashboard with real datasets and verify there is no performance issues.