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?
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"
)
)