DAX - Cascading Table Filters

By Lukas Hillesheim, 5 February, 2024
Blog Article & Copyright by Lukas Hillesheim
The following article shows how cascading 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 be either be located on the 1-side as on the N-side as well. Or in other words: given this relationship, 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].