cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Regular Visitor

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

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

1 ACCEPTED SOLUTION
Super User

Hello @AlvRomero,

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!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Super User

Hello @AlvRomero,

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!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning