Cascading filters data is not populating

[manojnoochila]

I'm experiencing an issue with cascading filters in Metabase. Specifically, when I select "All" in higher-level filters (such as "City" or "Neighbourhood"), the dependent filter (like "Township") displays only a single option, rather than all available options. Here's the SQL query I'm using:

sql

CopyEdit

SELECT
  COUNT(*) AS count
FROM
  cre_transactions
  INNER JOIN tower_extractions ON cre_transactions.latestTowerExtractionId = tower_extractions._id
  INNER JOIN towers ON tower_extractions.tower_id = towers._id
  INNER JOIN societies ON towers.society_id = societies._id
  INNER JOIN federations ON societies.federation_id = federations._id
  INNER JOIN townships ON federations.township_id = townships._id
  INNER JOIN neighbourhoods ON townships.neighbourhood_id = neighbourhoods._id
  INNER JOIN micromarkets ON neighbourhoods.micromarket_id = micromarkets._id
  INNER JOIN cities ON micromarkets.city_id = cities._id
WHERE
  1=1
  [[AND {{tower}}]]
  [[AND {{societies}}]]
  [[AND {{federations}}]]
  [[AND {{township}}]]
  [[AND {{neighbourhood}}]]
  [[AND {{micromarket}}]]
  [[AND {{truvaMicroMarketName}}]]
  [[AND {{city}}]]
  [[AND {{saleType}}]]
  [[AND {{dateRange}}]]
  [[AND {{truvaQualified}}]]

Steps to Reproduce:

1. Create a dashboard with cascading filters for "City," "Neighbourhood," and "Township."
2. Set the "City" filter to "All."
3. Observe that the "Neighbourhood" filter displays all options.
4. However, the "Township" filter shows only one option instead of all available options.

Additional Information:

• All relevant foreign key relationships are correctly defined in the data model.
• The filters are configured as "Category" types with multiple selections allowed.
• The SQL query uses INNER JOIN statements to connect the tables.

Troubleshooting Steps Taken:

• Verified foreign key relationships in the data model.
• Ensured filters are set to "Category" type with multiple selections allowed.
• Attempted to change INNER JOIN to LEFT JOIN in the SQL query to account for missing data in related tables.
• Cleared the browser cache and refreshed the dashboard.
• Checked Metabase logs for any errors related to filter operations.

Despite these efforts, the issue persists. Any insights or suggestions to resolve this problem would be greatly appreciated.
I have attached the image