Forum Discussion
Using a temporary table variable to simplify CALCULATE
You can use REMOVEFILTERS to remove the context filters from a table or column(s) from a table, so REMOVEFILTERS('Date'[End of week]) might work in both cases.
One thing to bear in mind though, particularly with date tables, is that other helper columns from that table can also be added to the filter, usually sort order columns. You can check which columns are being used by using Performance Analyzer to grab the DAX code generated for the visual and check it in DAX Studio. Make sure that you either include all of them in the REMOVEFILTERS or just use the entire Date table in there.
Thank you for your suggestion, John.
Regarding Formula 1: Using ALLEXCEPT(WorkOrders, WorkOrders[Order Type]), would clear all filters except filters on [Order Type]. However, there are other filters on other columns that I also want to keep. What I am looking for is to keep all context filters that affect the WorkOrders table, except for the Date[End of Week] context, which propagates via the relationship to the WorkOrders[Compliance Date] column. Does one clear the filter context on the Date[End of Week] column (which is on the axis of the visual) or the WorkOrders[Compliance Date] column? How does one clear only the one context filter whilst retaining all the others? I looked at KEEPFILTERS but struggle to understand how it works.
Regarding Formula 2: I discovered that it retains the context filter passed from Dates[End of Week] via the relationship to WorkOrders[Compliance Date] despite using
CALCULATE( COUNTROWS( WorkOrders ),
FILTER( ALL( WorkOrders[Compliance Date], WorkOrders[Finish Date] ),
How would one clear the context filter created by Dates[End of Week], shown on the axis on the visual, so that it does not filter the WorkOrders table, but still retain all other context filters? The concept is similar to calculating running/cumulative totals, but the examples I find on the Internet all use columns from the same table as the one filtered, not from related dimension tables.
BTW I have used both formulas successfully with fact tables that are unrelated to the Dates dimension; it is the relationship with Dates that introduces the additional context filtering that I am trying to get rid of.
Thanks
Hi fretief ,
//Does one clear the filter context on the Date[End of Week] column (which is on the axis of the visual) or the WorkOrders[Compliance Date] column? How does one clear only the one context filter whilst retaining all the others?
You can use the function "REMOVEFILTERS" which johnt75 mentioned. Or you can also try with ALL('Table'[Column]). You can find the difference between ALL(''Table) and ALL('Table'[Column]) here:
Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI.
//I looked at KEEPFILTERS but struggle to understand how it works.
You can check these:
KEEPFILTERS - DAX Guide - YouTube
//How would one clear the context filter created by Dates[End of Week], shown on the axis on the visual, so that it does not filter the WorkOrders table, but still retain all other context filters?
Same as the first question, try what johnt75 mentioned.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.