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. 


v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi, Anonymous 

 

Based on your description, you may create a calculated table with the following formula.

 

Test = DISTINCT('Table'[End Week])

 

 

Then you may create a measure as below.

 

Result = 
CALCULATE(
    SUM('Table'[Amount]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Date]<=SELECTEDVALUE('Test'[End Week])
    )
)

 

 

Result:

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    az38  & v-alq-msft 

    Thank you both! Once I created a seperate table to use as my selected dates and removed the relationship between them it works! 🙂