Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Dynamic Filter To Count Inactive IDs but only for filtered subset of data


I have attached sample data which shows a source, ID and date columns.


On my dashboard I have an imported visual, timeline slicer (timeline 2.4.0) which will allow the user to select different periods of data. E.g. if the timeline slicer has been selected as August 2022, the data will be between 01/08/2022 - 31/08/2022.


However the measures below should ignore the slicer and only take into account the last date selected by the user.  The calculation below should ignore the 01/08/2022 and instead filter the dataset down to date <= 31/08/2022.

The dashboard allows the user to also select a cut-off date. 

Assuming the last date selected is 31/08/2022, the first step is for the sample data attached to filter down to only show rows where the date is <= 31/08/2022. 

Of those rows, there is then another 'cut-off' date selected by the user. This could be 01/08/2022. 

Therefore, I need the MAX date per ID from the filtered data, where the data is <= 31/08/2022. 

For ID 123 that would be 23/08/2022 
For ID 456 that would be 25/08/2022
For ID 789 that would be 28/07/2022


From that filtered data, the max date should be compared to the cut-off date, 01/08/2022. If the date is before the cut off date then I would like to count the distinct ID's which match that criteria. In this case, only 789, so the result is 1.


The approach I have taken so far is (To keep things simple I have hardcoded the date for this question): 


var datefiltered = FILTER(table1, table1[Date] <= DATE(2022,08,31) && table1[source] IN {"Runs"})
This gives me a filtered table which match the criteria I want. 

var maxdatetable =        


    "ID", table1[ID],

    "Date2", table1[Date]))


The idea here is to take that filtered table and then get the max date per app id using max and allexcept but it does not work.

I've tried many variations including wrapping a calculatetable / trying to use my filtered table as a filter in a calculatetable expression but can't seem to get it to work. 


I have got this working using a disconnected table, e.g. not connected to the dates table I currently have which is made from a CALENDARAUTO() function. But, I need it to work without disconnecting as it is cleaner and better for user filtering. 


Would greatly appreciate any help the community can provide! 


Thank you! 

Frequent Visitor

Does anyone have any ideas? Or any more information I can provide? I can't seem to attach a file due to being a new member but I have linked to sample data above.

Hey @kush23456 ,


is that still open or could you solve the issue?


Best regards


Hi Denis,


I ended up solving it in a different way, happy to mark as solved.



Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors