Forum Discussion
Why does this formula work? Filter(all(...
Hi
The following formula allows me to sum YTD sales to a week selected on my date slicer:
YTD Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Number]<=max('Date'[Week Number]) && 'Date'[Week Year]= max('Date'[Week Year])))
So when I select week 5 on the slicer, it will sum up weeks 1 to 5. However I thought the 'FILTER(ALL('date')' part of the formula would remove my slicer filter because it removes all table filters does it now? I would therefore expect it to bring back ytd to the max week number.
What am I misunderstanding?
Anonymous
Yes, I know it is confusing...It takes a while to get your head around it (I've been at this for over 3 years and I learn stuff every day!).
We are back to a previous stage in the explanation. The whole FILTER expression contains 2 parts:
1) ALL removes the filters from the date table
2) THEN the second filter expression kicks in. What it's doing is seeing an unfiltered dates table (since ALL has removed the filters), and then it returns the values established in the second expression over the whole dates table. Since the second filter expression checks the week number column, which does have a value "selected", it applies the new filter based on that selected value.
Your second expression refers to the value selected in the week number column and a year. So it applies the filters accordingly over the whole date table.
18 Replies
Hi, Anonymous
When getting formatted via daxformatter.com, the measure is way much easier to analyse.
YTD Week = CALCULATE ( SUM ( 'order'[Qty] ), FILTER ( ALL ( 'Date' ), 'Date'[Week Number] <= MAX ( 'Date'[Week Number] ) && 'Date'[Week Year] = MAX ( 'Date'[Week Year] ) ) )To some extent, you're right about ALL('Date'); it does return a whole 'Date' table by removing any filters; but then it's filtered by conditions "'Date'[Week Number] <= MAX ( 'Date'[Week Number] ) && 'Date'[Week Year] = MAX ( 'Date'[Week Year] )".
