Forum Discussion

ARomain57's avatar
ARomain57
Frequent Visitor
1 year ago

Remove Dropdown filter from a measure

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. 

 



Need Help

2 Replies

  • marcelsmaglhaes's avatar
    marcelsmaglhaes
    Super User
    1 year ago

    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's avatar
      ARomain57
      Frequent Visitor
      1 year ago

      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