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

v-alq-msft's avatar
v-alq-msft
Community 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! 🙂