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.
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.
