Remove Dropdown filter from a measure

[ARomain57]

I am trying to create a measure that shows the last 5 attempts at getting consent when approached. However, I have a Year dropdown [Request Date] filter at the top of the page set to 2024, and sometimes, the last 5 attempts go back into 2023 or beyond.

The code below works, but it filters the last 5 attempts to 2024, due to the Year dropdown. I can't figure out how to ignore the dropdown (filtering by Request Date), but also use the same date (Request Date) in the measure. I have tried using ALL or REMOVEFILTERS, as I keep getting an error or wrong answer.

Last 5 =

VAR Last5Attempts = TOPN(5,FILTER('Table1',NOT(ISBLANK([Request Date])) && [Approached] = "Yes"),[Request Date],DESC) RETURN

COUNTROWS(FILTER(Last5Attempts,[Consent] = "Yes"))

Below is my page with the Year filter set to 2024. The year is using the Request Date.

The Last 5 attemps is the correct data using my code above. But again, it is limited to the 2024 dropdown. 

[marcelsmaglhaes]

Hey @ARomain57 

Try the meauser bellow:

Last 5 =
VAR Last5Attempts =
TOPN(
5,
FILTER(
ALL('Table1'[Request Date]),
NOT(ISBLANK([Request Date])) && [Approached] = "Yes"
),
[Request Date],
DESC
)
RETURN
COUNTROWS(FILTER(Last5Attempts, [Consent] = "Yes"))
-----------------------------------------------------------------------------------------------------------------------
***ALL('Table1'[Request Date]): This removes any external filters applied on Request Date (such as your Year dropdown), so you can still retrieve data beyond the year 2024.

[ARomain57]

I tried that before and it gives me an error.

A single value for column 'Approached' in 'Table1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specificying an aggregation. 

When I just use ALL('Table1'), I get the wrong value