Warm tip: This article is reproduced from serverfault.com, please click

How does CALCULATE apply all the filters?

发布于 2020-11-29 19:29:59

Each filter in CALCULATE is a table. Given more than one filter (say 3 separate filters), this results in 3 tables.

Does the resultant data (after all filters have been evaluated) have all the row/columns from the dataset after doing an AND with all the filter tables? How does CALCULATE apply all the filters?

Questioner
variable
Viewed
0
sergiom 2020-11-30 04:03:31

The filter are applied as the last step of CALCULATE execution. When more than one filter are used, they are applied with an AND operation. The resulting filter is the intersection of the existing filters.

For instance the expression

CALCULATE (
    COUNTROWS ( Sales ),
    Product[Color] = "Red",
    Product[Brand] = "Contoso",
    Customer[Continent] = "Asia"
)

will count the number of rows in table Sales related to Products that have Color = Red AND Brand = Contoso AND are sold to customers who live in Asia.

To better understand what are the filter tables, we must remember that each filter argument is expanded by DAX as its equivalent FILTER expression like follows:

CALCULATE (
    COUNTROWS ( Sales ),
    FILTER (
        ALL ( Product[Color] ),
        Product[Color] = "Red"
    ),
    FILTER (
        ALL ( Product[Brand] ),
        Product[Brand] = "Contoso"
    ),
    FILTER (
        ALL ( Customer[Continent] ),
        Customer[Continent] = "Asia"
    )
)