Forum Discussion
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 amount based on the selected [Week]. The measure functions fine for the most part, but there are certain projects that return a blank value. When digging into the actual data for these projects, they exist and do have a JTD amount that it should return. When they return a blank, I think it's because the project does not contain the SELECTEDVALUE(Table[Week]).
So, I started testing to check if it was the SELECTEDVALUE(Table[Week]) portion that was causing the blanks.
To test, I created a measure like this: CALCULATE(MAX(Table[Week]), ALLEXCEPT(Table, Table[Week]))
This works fine on it's own, but as soon as I add a filter for Table[Project] it returns a blank value.
All data is coming from the same table.
[Week] and [Date] are datetime
[Project] is text
[Amount] is number
Any ideas what I'm doing wrong?
15 Replies
- amitchandak
Super User
Anonymous What you are trying to achieve using these measures.
In case you are looking for this week , last week rolling week etc refer :https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
- az38
Community Champion
Anonymous
what else filter do you need? filter by project is included in my statement
- az38
Anonymous
try
CALCULATE(SUM(Table[Amount]), ALLEXCEPT(Table, Table[Project]), FILTER(ALL(Table), Table[Date] <=SELECTEDVALUE(Table[Week])))
