Filtering based on another filter


I am creating an analysis for a client/company that reads books. The dataset looks like this.

Company | Book_read | date_of_reading | etc…

Company1 | BookA
Company1 | BookA
Company1 | BookB
Company2 | BookA
Company3 | BookC
Company2 | BookC

Each company wants its own analysis of book read, but they shouldn’t see the other companies usage.

For example I need to create the analysis for Company1. So I have created a template and I have filtered all graphs on company=Company1.

However, company1 also want to filter its graph by book name. So I have created a filter Book_read. And I used “pin to top”.

However, when I (or the client) click on the dropdown book_name filter, all books are displaying (A,B and C) even though company1 never read book C ever.

How can I remove books that company1 never read from that filter?
Thank you.

Hi @pam

To remove books that Company1 never read from the book_name filter in your analysis, you can use a combination of filtering and calculated fields in quicksight:

1 - Create a calculated field: Start by creating a calculated field that identifies the books read by Company1. Let’s call this field “Books_Read_Company1”. The formula for this calculated field would be something like:

ifelse({Company} = ‘Company1’, {Book_read}, null)

this formula assigns the book name if the company is Company1, and assigns null otherwise

2- apply a filter: now, apply a filter on the calculated field “Books_Read_Company1” to exclude null values. This filter ensures that only books read by Company1 are included in the analysis

3 - modify the book_name filter: Finally, modify the book_name filter to use the calculated field “Books_Read_Company1” instead of the original book_read field. This will ensure that only the books read by Company1 are displayed in the book_name filter dropdown.

by following these steps, the book_name filter will dynamically adjust based on the books read by Company1, and only show the relevant options, other companies’ books will be excluded from the filter options for Company1

remember to repeat these steps for each company’s analysis, replacing the relevant company name and field references accordingly

This approach leverages calculated fields and filters in quicksight to provide customized analysis for each company while maintaining data privacy between different companies.

hope help’s


Thank you Blenner. That worked

1 Like