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 someti...
Need Help
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