Create a filter that is calculated based on the applied dynamic date filter

Hello Anonymous,

1. Create a measure to calculate the minimum ratio for each product in the selected date range.

Min Ratio in Date Range = 
VAR SelectedStartDate = MIN('Table'[Date])
VAR SelectedEndDate = MAX('Table'[Date])
RETURN
CALCULATE(MIN('Table'[Ratio]), DATESBETWEEN('Table'[Date], SelectedStartDate, SelectedEndDate))

2. Create a measure to filter the products that have at least one day with a bad ratio (lower than 90%) in the selected date range.

Products with Bad Ratio in Date Range = 
VAR MinRatio = [Min Ratio in Date Range]
RETURN
IF(MinRatio < 0.9, 1, BLANK())

3. Use the "Products with Bad Ratio in Date Range" measure as a visual-level filter in your report. This filter will show only the products that have at least one bad ratio day in the selected date range, but will show all the days for each selected product, not just the bad ones.

4. To show all products when the selected date range does not have any bad ratio days, you can create a measure that checks if any product has a bad ratio day in the entire data set (not just in the selected date range)

Has Any Product with Bad Ratio = 
IF(COUNTROWS(FILTER('Table', [Products with Bad Ratio in Date Range] = 1)) > 0, 1, BLANK())

5. Use the "Has Any Product with Bad Ratio" measure in a visual-level filter along with the "Products with Bad Ratio in Date Range" measure to show all products when there are no bad ratio days in the selected date range. For example:

• Set the "Products with Bad Ratio in Date Range" measure as a visual-level filter with the value 1 selected.
• Add the "Has Any Product with Bad Ratio" measure as another visual-level filter, and select both 1 and blank values.

This approach should give you the desired result without the need to add columns to the table. Hope this helps!