Forum Discussion
Helper IIIDynamic Filtering based on date selection
Thanks for sharing the files, and yes unfortunately direct file attachments are not available to all users. Last I checked it was Super Users only but that may have changed đ
From what I can see, all that potentially needs to be fixed is the definition of which Acquisition Dates to include relative to the "min date" (whether fixed or relative). I may have confused the definition with my earlier suggestion.
Should the Acquisition Date be at least 1 year earlier than the "min date"?
If so, these calculation item definitions seem to give your expected result.
I've used EDATE as well since it adds/subtracts a specificed number of months regardless of days per month.
/* =========================================================================== Cust Dynamic =========================================================================== */ VAR MinDate = MIN ( 'DATE'[ActualDate] ) VAR CompDynamic = CALCULATE ( SELECTEDMEASURE ( ), KEEPFILTERS ( CUSTOMERS[AcquisitionDate] <= EDATE ( MinDate, -12 ) && CUSTOMERS[Status] = "Active" ) ) RETURN CompDynamic /* =========================================================================== Cust Fixed =========================================================================== */ VAR MinDate = CALCULATE ( MIN ( 'DATE'[ActualDate] ), ALLSELECTED ( 'DATE' ) ) VAR CompFixed = CALCULATE ( SELECTEDMEASURE ( ), KEEPFILTERS ( CUSTOMERS[AcquisitionDate] <= EDATE ( MinDate, -12 ) && CUSTOMERS[Status] = "Active" ) ) RETURN CompFixedIf anything, the condition on CUSTOMERS[AcquisitionDate] should be the only thing to adjust.
Also, if it is good enough to check if a customer exists in SALES, I would also suggest adjusting Cust_CountSales as follows:
Cust_CounSales = COUNTROWS ( SUMMARIZE ( SALES, CUSTOMERS[CustomerID] ) )Is this closer to a solution?
Super UserHey GQ00 ,
You donât need calculated tables or calculation groups for this. The most efficient and flexible approach (especially in a composite model with DirectQuery fact) is to create a disconnected slicer table that controls a measure-based filter logic.
Since all visuals must respond to the customer filter, the cleanest pattern is:
1. Create a small disconnected table (e.g., âCustomer Filter Typeâ) with three rows:
All Customers
Active Customers â Dynamic
Active Customers â Fixed
Use this table in a slicer.
2. Create a measure that evaluates which option is selected and returns 1/0 depending on whether a customer should be included. Then apply this measure as a visual-level (or page-level) filter set to âis 1â.
Your logic would look like this conceptually:
Get the minimum selected date from the date slicer
For Dynamic version â respect visual date context
For Fixed version â remove date context using ALL or ALLSELECTED on Date
Apply condition:
AcquisitionDate <= MinDate - 365
AND Status = "Active"
The difference between option 2 and 3 should indeed be context handling:
Dynamic â uses current filter context (dates on X-axis affect it)
Fixed â removes date context using ALL('Dim Date') so it only reacts to slicer selection
Avoid calculated tables here because:
They wonât react dynamically to slicer context.
Theyâll increase model size unnecessarily.
They donât play well with DirectQuery performance.
Also avoid switching relationships unless absolutely required â your current star schema is correct.
The key idea is:
đ Let a disconnected slicer drive a measure.
đ Use that measure as a filter.
This keeps everything dynamic, efficient, and compatible with DirectQuery.
If performance becomes an issue, you may want to precompute acquisition year or a boolean âAcquired Last Yearâ flag in the dimension table, but only if the dynamic calculation becomes too heavy.
If it solved your issue, feel free to mark it as the solution so others can benefit too.
Thanks for being part of the community.
GQ00Hi MohdZaid_ , thanks for your time and feedback.
I tried following your steps, relying on AI for #2. I ended up with a disconnected filter for the three filter types, and a measure with a switch that looks for the selected filter and applies the relevant logic.
However it seems to be showing a similar behaviour as Owen's solution, in the sense that fixed cohort is exactly the same as dynamic.
Is there a limitation in Pbi that I'm not aware of?Additionally, not sure how I can filter the page when relying on a measure, and filtering each visual is not viable at this stage.
Is there anything I'm missing?- MohdZaid_
Hey GQ00 ,
This is not a Power BI limitation, but it usually happens because the date context is not being removed correctly.
In your âFixedâ logic, you must explicitly remove all date context coming from visuals, not just slicer context.
Very important distinction:
ALL('Dim Date') â removes all date filters (including slicer)
ALLSELECTED('Dim Date') â keeps slicer, removes visual context
No modifier â fully respects current visual context (dynamic behavior)
For your scenario:
Dynamic â use the current filter context normally
Fixed â compute MinDate using ALLSELECTED('Dim Date') so it only reacts to the slicer, not to axis context
If you used ALL('Dim Date'), both versions can accidentally evaluate the same way depending on how MinDate is calculated.
Youâre right â applying the measure to every visual is not scalable.
Unfortunately, Power BI does not allow measures as model-level filters.
However, you have two clean options:
Option 1 (Recommended): Use the measure in the Filter Pane at Page Level
You can:
Drag the measure into the Page filter pane
Set it to is 1
This applies to all visuals on the page without touching each one.
This works perfectly with disconnected slicers.
Option 2 (More Advanced / More Efficient)
Instead of filtering visuals, embed the logic inside your base measures.
For example:
Instead of:
[Total Sales]
Use:CALCULATE(
[Total Sales],
FILTER( Customers, [Customer Cohort Measure] = 1 )
)
This guarantees every measure respects the cohort logic automatically â but requires refactoring measures.For large composite models, this is often more stable and predictable.