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

[Anonymous]

Hi everyone! I would like to share with you my problem and hopefully find between all a great solution. My aim is to create a measure in order to use it like a filter.  This measure must take into account the dynamic date filter that is being applied, in such a way that if the dynamic date filter is modified, it will be recalculated. I will show you the data structure and what is the result I am looking for. The data we have is structured similar to this:

Date	Prrice "A"	Price "B"	ProductName	Ratio ("A" divided by "B")
2023-03-01	12	12	Apple	100%
2023-03-02	15	15	Apple	100%
2023-03-03	10	20	Apple	50%
2023-03-04	7	8	Apple	87'5%
2023-03-05	8	8	Apple	100%
2023-03-06	10		Apple

Ratio is a measure which we calculate previously and is added to the viz. The rest of the columns are original data (there are more products but the core data is shown). Now, we need to create a measure which filter which products have AT LEAST one day with a bad ratio (lower than 90%), BUT we want to see everyday in the date filter range for that product, not only the bad ones. For example, if the Date filter is bounded between March 01 and 02, "Apple" should not appear in the viz since it does not have any day with a bad ratio in the requested date range (The table above should be empty). But if the range of dates is limited between March 02 and 06 (included), we would like to see the data for the "Apple" product for the entire range of dates, not just for the bad days, so the viz it should be as follows:

Date	Prrice "A"	Price "B"	ProductName	Ratio ("A" divided by "B")
2023-03-02	15	15	Apple	100%
2023-03-03	10	20	Apple	50%
2023-03-04	7	8	Apple	87'5%
2023-03-05	8	8	Apple	100%
2023-03-06	10		Apple

I hope I have explained the problem clearly.
For the solution, I would like to know if there is a way to apply it without the need to add columns to the table, but if it is considered necessary, a solution that needs to add a new column will be appreciated as well.

Thank you so much in advance

[Sahir_Maharaj · Accepted solution]

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!