For example when there is a bar chart with multiple years of data but then after applying a filter there is no data for some years and then those years are excluded from the chart. How can the chart be forced to still show the same years before and after the filter with zero values when there is no data for a year due to the filter. The goal is to always show the same number of years for consistency.
QuickSight can only work with one dataset per visual. As a result, when your bar chart is filtered by the year column of your main table, it will not be shown. To solve this issue you will need to add zero data to your table. I explained how you can do it for another question here.
You will need to create multiple datasets and merge them together:
- A Year Table with two new columns: “Amount” with zero values, and a dummy column with the value 1.
- A Dimension Table with unique values for every dimension you have in your table, and a dummy column with the value 1.
- Your existing table.
Now, using the join mechanism you can create cartesian products between the Years and all other dimensions using the the dummy column with the 1 value. This will create a dataset of all years and other dimension permutations. Then, use this dataset and merge to it the original table using a left-join by the matching dimensions. Finally, add a calculated field that will sum the original Amount with the amount in the original Years table. For years you don’t have any data in the original table, you will have the zero from the Years table.
I hope it helps.