Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kush23456
Frequent Visitor

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

Hi,

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 =        

DISTINCT(
SELECTCOLUMNS(filteredtable,

    "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! 

4 REPLIES 4
kush23456
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

Denis

Hi Denis,

 

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

 

Thanks,

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors