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. 


  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    az38 

    This still returns as blank. Is the measure you provided missing the variable? You created a variable but didn't use it.

    Also, I want my selection to be the End Week, and not the Date because the data will be displayed on a graph showing JTD amounts by Week. 

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous 

    ok, I got it

    first, create a calendar table

    CalendarTable = CALENDAR(MIN('Table'[Date]), MAX('Table'[End Week]))

    then use as CalendarTable[Date] field as date slicer and create a measure in your table

    Measure = 
var _endDate = CALCULATE(MAX('CalendarTable'[Date]),ALLSELECTED('Table'[End Week]))
RETURN
CALCULATE(SUM('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[Project]),'Table'[Date]<=SELECTEDVALUE(CalendarTable[Date]) ))
  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    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

  • 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! 🙂