Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

ALLEXCEPT not ignoring filters

Ultimately I am building a measure that looks like this: CALCULATE(SUM(Table[Amount]), FILTER(ALLEXCEPT(Table, Table[Project]), Table[Date] <=SELECTEDVALUE(Table[Week]))) This calculates a JTD ...
Need Help
  • az38's avatar
    az38
    6 years ago

    @hahl

    Yes, exactly. wrong copy and paste 😞

    Measure = 
var _endDate = CALCULATE(MAX('CalendarTable'[Date]))
RETURN
CALCULATE(SUM('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[Project]),'Table'[Date]<= _endDate ))

    Безымянный.png

Anonymous's avatar
Anonymous
Not applicable
6 years ago

az38 

Apologies, I wasn't able to get the upload to work. Possibly due to working from home.

Here's the sample table I created:

Project Date End Week Amount

123450Friday, January 3, 2020Saturday, January 4, 20205
123450Wednesday, April 1, 2020Saturday, April 4, 20208
123450Wednesday, December 4, 2019Saturday, December 7, 201978
234500Wednesday, February 5, 2020Saturday, February 8, 202099
234500Sunday, March 8, 2020Saturday, March 14, 202010
234500Thursday, January 2, 2020Saturday, January 4, 202064
156700Thursday, January 2, 2020Saturday, January 4, 202087



The End Week column is just the ending date of the week which corresponds to the Date (transaction date).
I was able to replicate the issue pretty easily with this data.

When I add the measure,

CALCULATE(SUM('Table'[Amount]), ALLEXCEPT('Table', 'Table'[Project]), FILTER(ALL('Table'), 'Table'[Date]<=SELECTEDVALUE('Table'[End Week])))

and then select an End Week in the slicer, it works perfectly.
I selected Feb 8th and the total was 333, which is the sum of all amounts before or equal to Feb 8th. 

To test the Project filter issue, I selected Project 123450. With Feb 8th still as the selected End Week, I want it to return a value of 83 (all data before the date of Feb 8th), but instead it returns blank.

Apologies again, hopefully this works for you.