Forum Discussion
Dynamic Filter creation based on multiple criteria
I have multiple data sources that externally refresh at different periods of time. 1 monthly, the other every day. I need my visuals to only show complete data and thus it needs to filter out anything that is not complete. The definition of complete is a full cycle duration of at least 15 days, max 28 days. I have cycle periods calculated out by date and can calculate the total number of distinct days that have passed in the cycle using a measure. The problem is my dataset is dynamic and cycle periods are not static (by date).
Therefore, I can't use the measure to filter out incomplete cycles.
Any thoughts on a work around?
A visual-level filter can't directly use a measure to remove rows because measures are evaluated after the filter context has already been established. For a dynamic scenario like yours, it's usually better to create a flag that identifies whether a cycle is complete and use that in your visuals.
Here are a few approaches you could consider:
Option 1 (Recommended): Create a "Complete Cycle" flag
If your cycle boundaries are already defined, create a calculated table/column (or compute it upstream in Power Query/Dataflow) that determines whether each cycle is complete.
For example, a cycle is complete when:
It contains at least 15 distinct days.
The cycle has ended (or meets your business completion criteria).
Then use this flag as a visual, page, or report filter.
Complete Cycle = IF ( [Distinct Days in Cycle] >= 15 && [Cycle End Date] <= TODAY(), TRUE(), FALSE() )If [Distinct Days in Cycle] is a measure, consider calculating the flag during data preparation instead of as a calculated column.
Option 2: Use a Cycle dimension table
If you already have a table that defines your cycle periods, add attributes such as:
Cycle ID
Start Date
End Date
Distinct Day Count
Is Complete
Relate this table to your fact tables and filter on Is Complete = TRUE.
Option 3: Handle it during ETL
Since your sources refresh on different schedules (monthly vs. daily), another common approach is to determine "complete" during the ETL process:
Calculate cycle completeness in a Dataflow, Fabric Pipeline, or SQL.
Persist only completed cycles (or add a completion flag).
Let Power BI simply consume the prepared data.
This is often more efficient and easier to maintain than implementing complex DAX logic.
Overall, if the definition of "complete" depends on multiple datasets refreshing at different times, I would recommend moving that logic as close to the data source as possible (Power Query, SQL, Dataflow, or Fabric Pipeline). It simplifies your report and ensures all visuals consistently use the same business rule.
For more information:
DAX filter context: https://learn.microsoft.com/power-bi/transform-model/desktop-quickstart-learn-dax-basics
Star schema guidance: https://learn.microsoft.com/power-bi/guidance/star-schema
💡 Helpful? Give a Kudos 👍 — keep the community growing.
✅ Solved your issue? Mark this as the Accepted Solution ✔️
Best regards, Prince Singh | Data Science & Microsoft Fabric Enthusiast
5 Replies
You can use a measure directly as a visual level filter, even if measures cannot be dropped into a slicer. Open the visual's Filters pane, drag your distinct days measure into "Filters on this visual", and set the condition to "is greater than or equal to 15". Power BI evaluates the measure per row of the visual, so cycles under the threshold get hidden and it stays dynamic as data changes.
If you also want incomplete cycles removed from other measures or totals, wrap them like this:
Sales Complete = IF ( [Distinct Days in Cycle] >= 15, [Sales] )
Any cycle that returns BLANK will not render in the visual by default. That gives you the completeness filter without touching the model.
If that resolved it, please mark it as the accepted solution and give it a kudos.
Best regards,
Shai KarmaniA visual-level filter can't directly use a measure to remove rows because measures are evaluated after the filter context has already been established. For a dynamic scenario like yours, it's usually better to create a flag that identifies whether a cycle is complete and use that in your visuals.
Here are a few approaches you could consider:
Option 1 (Recommended): Create a "Complete Cycle" flag
If your cycle boundaries are already defined, create a calculated table/column (or compute it upstream in Power Query/Dataflow) that determines whether each cycle is complete.
For example, a cycle is complete when:
It contains at least 15 distinct days.
The cycle has ended (or meets your business completion criteria).
Then use this flag as a visual, page, or report filter.
Complete Cycle = IF ( [Distinct Days in Cycle] >= 15 && [Cycle End Date] <= TODAY(), TRUE(), FALSE() )If [Distinct Days in Cycle] is a measure, consider calculating the flag during data preparation instead of as a calculated column.
Option 2: Use a Cycle dimension table
If you already have a table that defines your cycle periods, add attributes such as:
Cycle ID
Start Date
End Date
Distinct Day Count
Is Complete
Relate this table to your fact tables and filter on Is Complete = TRUE.
Option 3: Handle it during ETL
Since your sources refresh on different schedules (monthly vs. daily), another common approach is to determine "complete" during the ETL process:
Calculate cycle completeness in a Dataflow, Fabric Pipeline, or SQL.
Persist only completed cycles (or add a completion flag).
Let Power BI simply consume the prepared data.
This is often more efficient and easier to maintain than implementing complex DAX logic.
Overall, if the definition of "complete" depends on multiple datasets refreshing at different times, I would recommend moving that logic as close to the data source as possible (Power Query, SQL, Dataflow, or Fabric Pipeline). It simplifies your report and ensures all visuals consistently use the same business rule.
For more information:
DAX filter context: https://learn.microsoft.com/power-bi/transform-model/desktop-quickstart-learn-dax-basics
Star schema guidance: https://learn.microsoft.com/power-bi/guidance/star-schema
💡 Helpful? Give a Kudos 👍 — keep the community growing.
✅ Solved your issue? Mark this as the Accepted Solution ✔️
Best regards, Prince Singh | Data Science & Microsoft Fabric Enthusiast
Thank you, Prince0011 for the detailed response! Option 2 is ultimately where I landed and it worked after some clever relationship tagging.
Hi unknown917 ,
Just to make sure I understand your scenario correctly: are you saying that the cycles do not necessarily start at the beginning of a month, and that each item may have its own cycle dates?
For example, suppose Item A has four cycles ending on the 3rd, 9th, 16th, and 28th ( and these are not exactly 3rd,9th,... of the selected month) . Do you want the dashboard show item A only when its third or fourth cycle has been completed?
Also, I assume you already have a Date dimension in your data model. Is that correct?
Thankyou Selva-Salimi , Prince0011 , Shai_Karmani for Addressing the issue.
Hi unknown917 ,Thank you for reaching out to Microsoft Fabric Community Forum,
Good to hear that you resolved your issue by using a cycle dimensional table in future Let us know if you need any further assistance? feel free to reach out to fabric community.
Thanks,
Chaithanya.
