Forum Discussion
Why does this formula work? Filter(all(...
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.
PaulDBrown you must frequently hit your head off a table in frustration dealing with learners like me!
"The slicer comes back into play because the filter expression refers to the column (Date [Week Number]) used in the slicer" - so our ALL function never removes the role of the slicer, it unfilters the data table but the slicer is still 'alive' so when filter expressions are used it filters with the slicer again?
Anonymous
Correct. The ALL function removes a specified filter on a table/column (it doesn't "erase" a filter)
The filtered column ("Slicer") comes into play again because the filter expression explicitly refers to that column. In this case, the MAX function is returning the selected value in the filtered column ("Slicer").
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.
Sorry Paul, this is the bit that still doesnt add up:
The filtered column ("Slicer") comes into play again because the filter expression explicitly refers to that column - this column shouldnt have a filter now though because 'ALL' removed it. This specific issue is what's causing the confusion for me.
Sorry if you've already answered this in a different way previously.
Thank you PaulDBrown and CNENFRNL , truly appreciated your help!