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.
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, John. REMOVEFILTERS does the job. This formula works:
W/Orders O/Due (Related Date No Tmp Table) =
VAR SelectedEoWeek = SELECTEDVALUE( DatesRelated[End of Week] ) --- Relevant week end date obtained from visual
RETURN
CALCULATE( COUNTROWS( WorkOrders ),
REMOVEFILTERS( DatesRelated ),
FILTER( ALL( WorkOrders[Compliance Date], WorkOrders[Finish Date] ),
(
WorkOrders[Finish Date] = BLANK() && --- WO does not have a finish date (still open)
WorkOrders[Compliance Date] <= SelectedEoWeek --- SelectedEOWeek is after Compliance Date
)
||
(
WorkOrders[Finish Date] <> BLANK() && --- WO has a finish date (closed)
WorkOrders[Compliance Date] <= SelectedEoWeek && --- SelectedEOWeek falls between Compliance Date...
SelectedEoWeek < WorkOrders[Finish Date] --- ... and Finish Date
)
)
)
However, I don't understand the logic: I would have thought that REMOVEFILTERS( DatesRelated ) would be sufficient to clear filters created by the visual on the DatesRelated dimension, and therefore the WorkOrders fact table would not be filtered by any dates. However, when I replace
FILTER( ALL( WorkOrders[Compliance Date], WorkOrders[Finish Date] ),
with FILTER( WorkOrders , the dates context is imposed again. Why does one have to clear filters on both the dimension and fact table dates columns? What is the sequence of steps here, first apply REMOVEFILTERS and then FILTER, or first FILTER then REMOVEFILTERS?
I have spent about three days on this, reading many articles including the following ones that deal with the same topic:
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Removefilters-not-working/m-p/1488132
https://community.powerbi.com/t5/DAX-Commands-and-Tips/RemoveFilters-is-not-working/m-p/2524570
https://community.powerbi.com/t5/Desktop/Removefilters-not-working-with-Date-Hierarchy/m-p/2196968
https://www.reddit.com/r/PowerBI/comments/pmbmh3/removefilter_does_not_remove_the_context_filter/
https://dax.guide/calculate/
https://www.sqlbi.com/articles/order-of-evaluation-in-calculate-parameters/
A description of what CALCULATE actually does in its black box would be very helpful.