Blog Article & Copyright by Lukas Hillesheim
The following article shows how repeated calls of CALCULATETABLE can be used to transport a filter over multiple hops. Use cases for this pattern are tables within a Galaxy schema or tables that are part of a more complex Snowflake schema.
1. Rules for the application of cascading CALCULATETABLE
The filtering table can reside on the 1-side or on the N-Side of a reltionship
Scenario: T1 -> T2
In scenario 1, there are two tables, T1 and T2 connected by a relationship with T2 on the 1-side. The table used for filtering can either be located on the 1-side as on the N-side as well. Or in other words: according to this relationships, T1 can be used to filter T2 and vice versa.
- // In either cases mentioned below, the filter is transferred:
- CALCULATETABLE ( T2, T1 )
- CALCULATETABLE ( T1, T2 )
Relationships are transitive
Scenario: T1 -> T2 -> T3
As in the example above, the arrows point to the 1-side.
- // Both statements cause the filter to be transferred:
- CALCULATETABLE ( T3, T1 )
- CALCULATETABLE ( T1, T3 )
At the point where a reversal of the direction occurs, CALCULATETABLE must be applied repeatedly
Szenario: T1 -> T2 <- T3 oder
Scenario: T1 <- T2 -> T3
In both cases, a reversal of direction takes place at T2.
- // Both statements cause the filter to be transferred:
- CALCULATETABLE ( T3, CALCULATETABLE ( T2, T1 ) )
- CALCULATETABLE ( T1, CALCULATETABLE ( T2, T3 ) )
- // Both statements cause the filter NOT to be transferred:
- CALCULATETABLE ( T3, T1 ) )
- CALCULATETABLE ( T1, T3 ) )
In order to cascade a filter, the first filter must already be filtered
This rule is self-explaining and should not need to be mentioned, because a filter that does not exist cannot be transferred. Due to the complexity of the statements and the fact that not all table expressions perform a context transition, this fact can be overlooked.
2. Schematic example
A filter is to be transferred over 7 hops. The initial filter resides on T1, the last one resides on T7. The objects marked in yellow show the points where the relationships change direction.
Figure 1: Cascading Filter Systematics
Figure 2: Solution Pattern 1
Figure 3: Solution Pattern 2
The second variant (figure 3) ensures that the initial filter is instantiated on T1.
3. Practical example in the Galaxy Scheme
Figure 4: Galaxy Schema
The example shows 4 tables that are part of a Galaxy Schema:
[Product] <- [FactSales01] -> [Time] <- [FactPlan]
Solution:
Figure 5: Solution
In the code example the operation starts on [Product]:
- EVALUATE(
- ADDCOLUMNS(VALUES('Product'[ProdName]) ...
Measure (Computed Column) [SumActualAmount]
Since [Product] and [FactSales01] are directly related to each other, a filter is created from the respective product row with the help of CALCULATE and transferred to the [FactSales01] table.
The additional restriction to year = 2018 was placed into the example because the data from table [FactPlan] is budget data that contains only values for 2018. Since [FactSales01] is restricted to 2018, the aggregates from both fact tables can be brought together producing the correct meaning.
Measure (Computed Column) [SumPlannedAmount]
The value for [SumPlannedAmount] is achieved using a cascading filter. [FactSales01] represents the start object for the cascade. Using "CALCULATETABLE( FactSales01 ... " ensures that [FactSales01] is already filtered when the operation starts: for each product, a filter is created on the [Product] table and applied to [FactSales01]. The additional restriction to the year 2018 has the same technical sense as above.
Step by step the filter on [FactSales01] travels over the table chain. These are [Time] and [FactPlan].