When using whole tables as filter arguments to CALCULATE, you are using an expanded version of the model, which consists of the table used in the arguments, as well as all other tables in the data model that are linked to that table through a 1-* relationship.
We will explain this concept with a small dataset containing only a few columns.
Basics
We will use 4 different measures and observe their results in the visual:
TotalQTY = SUM(FactSales[OrderQuantity]) ALL_QTY_CustomerKey = CALCULATE([TotalQTY],ALL(DimCustomer[CustomerKey])) ALL_QTY_BusinessType = CALCULATE([TotalQTY],ALL(DimCustomer[BusinessType])) ALL_QTY_FactSales = CALCULATE([TotalQTY],ALL(FactSales))
First, we will observe the measure ALL_QTY_BusinessType. This measure uses CALCULATE remove filter argument ALL(DimCustomer[BusinessType]) to ignore the Business type on the visual. This is why there is always the same figure of 205788 shown for all 3 different types of business.
The measure ALL_QTY_FactSales holds more complexity since it operates on the expanded table. Even though we added a single ALL(FactSales) table filter as an argument to CALCULATE function, the measure is also ignoring filters coming from DimProduct and DimCustomer tables. We haven’t explicitly added those tables as filter arguments, but they are included in the ALL(FactSales) as expanded tables linked to the FactSales table through a 1-* many relationships.
The expanded table feature is always present but only gets utilized in specific situations, such as using tables as CALCULATE remove filter arguments.
The expanded table of FactSales one contains all columns from dimension tables connected to Fact table through 1-* relationship. This is why, when we ignored FactSales table using ALL(FactSales) argument in CALCULATE, we also ignored all columns coming from dimension tables. Expanded tables feature expands to every other table that has a 1-* relationship with an already existing one, often known as snowflake term.
Key Columns Explained
If we observe the ALL_QTY_CustomerKey in the visual above, we can see that it returns the same values as the TotalQTY measure. We might think that because we ignored a key column as an argument to CALCULATE, we will activate the expanded table feature, but that is not the case.
ALL(DimCustomer[CustomerKey]) will ignore the key column coming from the FactSales table, but will not propagate to the DimCustomer dimension or ignore DimCustomer[CustomerKey] column, even though it is a key pair on the 1 side of the relationship!
Only the complete table reference will expand filter to all dimensions on the 1 side of the relationship.
