CODEWITHSUNDEEP
powerbidaxpowerbi-desktopmicrosoft-fabric
variable
variable

Reputation: 9724

Why use REMOVEFILTERS in the absence of date table?

In the absence of date table, why does the following article recommend to use REMOVEFILTERS?

https://www.daxpatterns.com/cumulative-total/

Sales Amount RT :=
VAR LastVisibleDate =
    MAX ( 'Date'[Date] )
VAR FirstVisibleDate =
    MIN ( 'Date'[Date] )
VAR LastDateWithSales =
    CALCULATE (
        MAX ( 'Sales'[Order Date] ),
        REMOVEFILTERS ()  -- Use ALL ( Sales ) if REMOVEFILTERS () and ALL ()
                           -- are not available
    )
VAR Result =
    IF (
        FirstVisibleDate <= LastDateWithSales,
        CALCULATE (
            [Sales Amount],
            'Date'[Date] <= LastVisibleDate
        )
    )
RETURN
    Result

It is important that the Date table is marked as a date table for the formula to work. If not, it is necessary to add REMOVEFILTERS over Date as a further CALCULATE modifier, when applying the filter in the computation of the Result variable:

VAR Result =
    IF (
        FirstVisibleDate <= LastDateWithSales,
        CALCULATE (
            [Sales Amount],
            'Date'[Date] <= LastVisibleDate,
            REMOVEFILTERS ( 'Date' ) 
        )
    )

Upvotes: 0

Views: 41

Answers (1)

ThxAlot
ThxAlot

Reputation: 101

To answer your question in the context of absence of date table, it's still necessary to switch back to the context of date table being PRESENT.

There's a hidden intricacy, pointed out by the authors in The Definitive Guide to DAX 2nd Edition

Whenever a filter is applied to a column of type Date or DateTime that is used in a relationship between two tables, DAX automatically adds an ALL to the entire Date table as an additional filter argument to CALCULATE.

it demystifies the behavior under the hood of some seemingly wrong DAX measure like an example in the above-mentioned book,

CALCULATE(
    CALCULATE(
        [Sales Amount],
        AND(
            'Date'[Date] >= DATE( 2007, 1, 1 ),
            'Date'[Date] <= DATE( 2007, 04, 30 )
        )
    ),
    'Date'[Year] = 2007,
    'Date'[Month] = "April"
)

What's really happening unseen is this,

CALCULATE(
    CALCULATE(
        [Sales Amount],
        AND(
            'Date'[Date] >= DATE( 2007, 1, 1 ),
            'Date'[Date] <= DATE( 2007, 04, 30 )
        ),
        ALL( 'Date' ) -- This is automatically added by the engine
    ),
    'Date'[Year] = 2007,
    'Date'[Month] = "April"
)

Now, get back to your question, the recommendation of manually adding REMOVEFILTERS ( 'Date' ) is an equivlant to the automatically added ALL( 'Date' ) (these 2 syntaxes are interchangeable in such scenarios).

BTW, even if with such a user-friendly behavior by DAX engine with date table being marked, I still stumbled on complex use cases where measures required explicit ALL( 'Date' ) to modify certain certain evaluation context. Next time, when measures don't return expected results, especially for measures incorporating time intelligence functions, it's worth thinking about this specialty.

Upvotes: 1

Related Questions