I have two data sources in tableau (A and B). The relationship is 1:n. Table A (main Table) with columns ID (Primary Key), Field 1, Field 2... Table B with columns ID (not PK), Field X, Field Y.
I want to use table B for filtering by Field X and Field Y and then in a related sheet plot data from table A with the filter that in SQL would be equivalent to WHERE A.ID IN (SELECT B.ID FROM TableB B) where Table B would already be filtered by the values of Field X and Field Y.
After some research I have found two options that do not convince me:
Option 1: Use a common inner join between the two tables and then use aggregation functions like AVG and COUNT DISTINCT on the measures of table A to avoid duplication.
Option 2: Use custom SQL on table A based on a parameter that is sent from Tableau and filter Table A at a database level.
The best option would be to have a join between sources in the following structure
SELECT A.* FROM tableA A INNER JOIN (SELECT DISTINCT ID FROM TABLE B WHERE Field X IN (Dynamic selection from Tableau) AND Field Y IN (Dynamic selection from Tableau) ) B ON A.ID = B.ID
Is such a thing possible?
Thanks!!