Using Column names VS using A whole table in ALL Function (DAX)

Question

Okay, SO I am trying to understand the behavior of FILTER(ALL) Function in DAX. I have created two DAX Expressions

1.

 sales 100 using table in ALL = 
    CALCULATE(
        [Customer sales],
        FILTER(
            ALL('Sales by Store'),
            'Sales by Store'[quantity_sold] * 'Sales by Store'[unit_price] >100
        )
    )

sales 100 using column names in ALL = 
CALCULATE(
    [Customer sales],
    FILTER(
        ALL('Sales by Store'[quantity_sold], 'Sales by Store'[unit_price]),
        'Sales by Store'[quantity_sold] * 'Sales by Store'[unit_price] >100
    )
)

In the Image above, Why is that when use a table name in the ALL function, we get the values in all categories. But, when we use the column names, we only get it where the value is more than 100, which is only one category, coffee beans.

sergiom sergiom · Accepted Answer · 2020-12-22T23:32:15

In the first formula, the effect of using the whole table is to remove any existing filter on the table. Since internally DAX uses expanded table, this means that it also removes filters set using dimensions, when existing.

With the second formula instead, only two columns are used, therefore other existing filters are not removed and therefore the [Customer Sales] measure is filtered by the new filter context set by the FILTER(ALL...) expression, but at the same time is also filtered by the pre-existing filter over product_category.

A possible way to make the second formula behave like the first one is to explicitly remove the other filters using REMOVEFILTERS (or ALL, if REMOVEFILTERS is not yet supported)

sales 100 using column names in ALL = 
CALCULATE(
    [Customer sales],
    FILTER(
        ALL('Sales by Store'[quantity_sold], 'Sales by Store'[unit_price]),
        'Sales by Store'[quantity_sold] * 'Sales by Store'[unit_price] >100
    ),
    REMOVEFILTERS( 'Sales by Store' ) // or any dimension, if used
)

To make the first one behave like the second one, it's possible to use KEEPFILTERS, that makes the FILTER expression to be added to the filters instead of replacing the pre-existing ones.

 sales 100 using table in ALL = 
    CALCULATE(
        [Customer sales],
        KEEPFILTERS( 
            FILTER(
                ALL('Sales by Store'),
                'Sales by Store'[quantity_sold] * 'Sales by Store'[unit_price] >100
            ) 
        )
    )

Using Column names VS using A whole table in ALL Function (DAX)

1 Answers