Using a temporary table variable to simplify CALCULATE

[fretief]

My data model consists of the following two tables (stripped of columns not relevant to this post):

Dates: [Date], [End of Week]   WorkOrders: [Compliance Date], [Finish Date], [Order Type]

The tables are related Dates[Date] 1:* > WorkOrders[Compliance Date]

I want to calculate the number of overdue work orders at the end of every week, which consists of:
1) work orders that are not yet finished ( [Finish Date] is BLANK ); plus
2) work orders that were finished after their [Compliance Date]

Both the following formulas work:

W/Orders O/Due by EoWeek 1 =                                                       --- Formula 1
VAR SelectedEoWeek = SELECTEDVALUE( Dates[End of Week] ) --- Relevant week end date obtained from visual
VAR ODueOrders =

FILTER( ALL( WorkOrders ), 

( 

WorkOrders[Finish Date] = BLANK() &&                    --- WO does not have a finish date
WorkOrders[Compliance Date] <= SelectedEoWeek --- SelectedEoWeek is after Compliance Date

)
||
( 

WorkOrders[Finish Date] <> BLANK() &&                        --- WO has a finish date
WorkOrders[Compliance Date] <= SelectedEoWeek && --- SelectedEoWeek falls between Compliance Date...
SelectedEoWeek < WorkOrders[Finish Date]                  --- ... and Finish Date )

) 

RETURN COUNTROWS( ODueOrders ) 

W/Orders O/Due by EoWeek 2 =                                                       --- Formula 2
VAR SelectedEoWeek = SELECTEDVALUE( Dates[End of Week] ) --- Relevant week end date obtained from visual
RETURN

CALCULATE( COUNTROWS( WorkOrders ),

FILTER( ALL( WorkOrders[Compliance Date], WorkOrders[Finish Date] ),

(

WorkOrders[Finish Date] = BLANK() && --- WO does not have a finish date
WorkOrders[Compliance Date] <= SelectedEoWeek --- SelectedEoWeek is after Compliance Date

)
||
(

WorkOrders[Finish Date] <> BLANK() && --- WO has a finish date
WorkOrders[Compliance Date] <= SelectedEoWeek && --- SelectedEoWeek falls between Compliance Date...
SelectedEoWeek < WorkOrders[Finish Date] --- ... and Finish Date

)

)

)

I prefer the format of Formula 1

VAR Temptable = FILTER( filterexp1 )
RETURN COUNTROWS( Temptable )

to the format of Formula 2

CALCULATE( 

COUNTROWS ( Table ),
FILTER( filterexp2 )

)

because I can stick the FILTER( filterexp1 ) command for the Temptable into DAX Studio and debug my code whereas I have yet to find a way to visualise what DAX is committing behind the scenes in the case of the second format. The format of Formula 1 is also much more readable when, for instance DIVIDE( COUNTROWS(TempTableSubset), COUNTROWS(TempTable) ). 

Questions:

1) The ALL( WorkOrders ) statement in Formula 1 clears all context filters, which means filters on WorkOrders[Order Type] are also cleared. The total number of overdue work orders is calculated correctly, but not the number of overdue orders by Order Type. How would one modify Formula 1 to only clear the context filter on the Compliance Date, but retain other context filters? I have tried various options but can't get the desired result. Almost all the documentation I find uses the format of Formula 2.

2) Assuming that one can develop a variation of Formula 1 that produces the same result as Formula 2, is there a reason (e.g. performance) why one formula is preferable over the other?

Thank you for taking the time to read & respond.

[fretief]

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

[Icey]

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

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.

[johnt75 · Accepted solution]

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.

[fretief]

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.

[johnt75]

You could replace the ALL(WorkOrders) with ALLEXCEPT(WorkOrders, WorkOrders[Order Type])

As for whether 1 is preferable to the other, you would need to run server timings and query plan against both to make sure, but I would doubt that there is a significant performance difference between the 2.