Dynamic Filtering based on date selection

[GQ00]

Hi,

I want to allow users to filter for customers based on acquistion date. The report has a date table, Dim customer and Fact Sales. Relationships are 1 to *

Dim Customers> Fact Sales
Dim Date > Fact Sales

Report includes a date slicer (in between) from Dim Date.

There should be three options to filter customers

1) all customers (aka no filter)

2)Active customers acquired last year - dynamic (based on min date selection of slicer> Acquisition date +365) & Status = Active

3)Active customers acquired last year - fixed (based on min date selection of slicer> Acquisition date +365 but ignoring context) & Status = Active

The goal is to allow the customer to filter based on a slicer.

The model is composite, where fact sales is a direct query. All measures and visuals should be impacted by the filter

I think the difference between 2 and 3 should be that visual context (i.e. dates in X axis) would impact the number of customers of 2, adjusting for it somewhat simple, by adding an all selected (Date) to the logic. However, my main challenge is that I do not know what is the most efficient or straightforward way of achieving this dynamic filter.

Calculated table, calculation groups, use relationship, I'm lost and I feel that Copilot is confusing me more.

Any help would be greatly appreciated. I can forsake simplicity for increased efficiency, as the mode is quite big.

Thanks in advance!

EDIT: I'm happy to add things to the filter pane

[MohdZaid_]

Hey @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.

[GQ00]

Hi @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.

[OwenAuger]

Hi @GQ00 

I would recommend creating a calculation group for this. With a calculation group, you would create a calculation item for each of the Customer filter options, then filter on the required calculation item to apply that logic to any required measures.

Based on your description, the expressions for each of the calculation items would be something like:

-- 1. All Customers
SELECTEDMEASURE ()

--2. Active customers acquired last year - dynamic
VAR MinDate =
    MIN ( 'Dim Date'[Date] )
RETURN
    CALCULATE (
        SELECTEDMEASURE (),
        KEEPFILTERS ( MinDate > 'Dim Customers'[Acquisition Date] + 365 )
    )

--3. Active customers acquired last year - static
VAR MinDate =
    CALCULATE (
        MIN ( 'Dim Date'[Date] ),
        ALLSELECTED ( 'Dim Date' )
    )
RETURN
    CALCULATE (
        SELECTEDMEASURE (),
        KEEPFILTERS ( MinDate > 'Dim Customers'[Acquisition Date] + 365 )
    )

The 1st calculation item leaves all measures unchanged.

The 2nd & 3rd calculation items evaluate measures with a filter on 'Dim Customer'[Acquisition Date] intersected with the current filter context (due to KEEPFILTERS).

Once you have created the calculation group, you can filter on a calculation item however you like, such as with a single-selection slicer or filter pane.

Are you able to get something like this working?

[GQ00]

Hi Owen, thanks for your reply.

Your approach is so simple and easy to follow, I'm impressed.

I've gone ahead and created a new calculation group, with a precedence =1. The CG includes 3 calculation items based on your response. I've then used the column in a single select slicer. 

The All and dynamic filters are working as intended, the static however, is not.

For the static I was expecting to see a fixed amount of customers, depending on the minimum selected date from the slicer; however a bar chat with a date on X axis shows this cohort grows over time. 

I'm not an expert but maybe the issue lies in the fact that the X axis of the visual redefines the min date despite using the all selected?

The image attached below is a visual representation of the issue. 28 customers in 2024; however only 23 were active with 12 months of trading in Jan 2024. I was expecting the chart to always show 23; not continue growing to 28.

Let me know if the above does not make sense and I can work towards producing a sample dataset, since I can't share the org's data for obvious reasons.

[OwenAuger]

@GQ00 

Glad to see that the calculation group approach is at least partially working!

To figure out what's going on in the static case, a sample model with dummy data, along with expected measure results would be helpful (when you can put one together).

In the meantime, could you share these:

• Definition of the measure on the y-axis of the column chart
• Semantic model diagram

In case it's any help, I put together an example using a Contoso Data Generator sample PBIX.

I adjusted the calc item "filter definition" for my version so the Dynamic calc item expression is:

VAR MinDate =
    MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        SELECTEDMEASURE (),
        KEEPFILTERS (
            Customer[Acquisition Date] >= MinDate - 365
            && Customer[Acquisition Date] < MinDate
        )
    )

 (I realise you have to add the "Active" filter as well.)

[GQ00]

Thanks for sticking this one out.

Sample Data & PBIX attached. 

Put it simply, Cust 1 comps from Dec 25; Cust 2 fr Feb 26.

I was expecting the Cust Fixed filter to filter based on min date from slicer, (Jan 26 in this case) but Cust 2 is still in the visual; 😵. 
Similarly , Cust 3 should only appear in Cust Dynamic not Cust Fixed

Gosh I'm struggling to add the attachments... 

[GQ00]

Apparently, new users can only share links in this forum... how inconvenient.

Anyway, here it is, PBIX and sample dataset, thanks for your help Owen!

https://drive.google.com/drive/folders/1ZsZM-4tebxytoD7Zxfgz9yPYpuVAtKli?usp=drive_link

[OwenAuger · Accepted solution]

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
    CompFixed

If 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?

[GQ00]

Thanks for your patience, Owen. You are a superstar, this works like a charm.

So all we I missed was using all selected on the entire table, like you initially suggested. That was producing blanks at first, hence why I changed it to column; but I may have had something else affecting the output.

I really need to familiarise myself more with the difference between selecting the table or column in all selected; unsure why if the slicer relies on actual date, all select on actual date didn't work.

Regardless, thank you for your time, patience, and thoroughness, you indirectly enabled a small business to be more data savvy.

Have a great day!

EDIT: also appreciate your feedback reg customer count, due to other reports, I can't summarise the sales table by customers ID, since it also includes # of customers by product and the like, but I can appreciate why your approach - whenever possible - is preferred, thanks.