Expanded Tables. DECODED!


In the above data model, Product has one-to-many relationship with Sales on ProductID column.

Total Sales Order Qty = SUM(Sales[OrderQty])

Assume you have a table visual with Product[Color] and above measure. It is obvious you know what the result will be. The visual will list the total sales order quantity against each product color.

How will the query engine calculate the result: Will it query the Product table first to get the colors, and then the Sales table to get SUM(Sales[OrderQty]) for each color returned by the previous query?

Before you start reading further, you should know that table expansion happens only to tables with regular relationship (one-to-one or many-to-one on tables in the same island or continent).

When is it expanded? What will be included when a table is expanded? Does this increase the size of the data model? Let’s find out.

In the above data model, Sales and Product table are imported. They have many-to-one relationship. When a calculated column in Sales table uses RELATED() function on a column of the Product table (or) when a measure aggregates the value in Sales table is evaluated, only then Sales table is expanded. This means, when the Sales table is queried, it will include related and relevant fields from the Product table by using left outer join.

Measure #1 Total Sales by Sale’s Unit Price

Total Sales by Sale's Unit Price = 
SUMX(
    Sales, 
    Sales[OrderQty] * Sales[UnitPrice]
)

Scenario 1: Total Sales by Sale's Unit Price measure is the only field used in the visual. What will be the result?

Did table expansion happen? Filter context is empty. No field of Product is referred within and outside this expression. So, storage engine executes only one query on the Sales table. Sales table is not expanded.

SET DC_KIND="AUTO";
WITH
	$Expr0 := ( PFCAST ( 'Sales'[OrderQty] AS  INT ) * PFCAST ( 'Sales'[UnitPrice] AS  INT )  ) 
SELECT
SUM ( @$Expr0 )
FROM 'Sales';

Scenario 2: Product[Color] column and Total Sales by Sale's Unit Price measure are used in the visual. What will be the result?

Did table expansion happen? Yes. Total Sales by Sale's Unit Price measure is evaluated for each product color. So, the Product[Color] is in the filter context. As, Product table is on the one-side of the relationship with Sales table, storage engine expands the Sales table to include Product[Color] and its total sales by executing a single left outer join query between Sales table and Product table [line 7, 8]. Grant total 714002.9136 is calculated by the formula engine.

SET DC_KIND="AUTO";
WITH
	$Expr0 := ( PFCAST ( 'Sales'[OrderQty] AS  INT ) * PFCAST ( 'Sales'[UnitPrice] AS  INT )  ) 
SELECT
'Product'[Color],
SUM ( @$Expr0 )
FROM 'Sales'
	LEFT OUTER JOIN 'Product' ON 'Sales'[ProductID]='Product'[ProductID];

Measure #2 Total Sales by Sale’s Unit Price with Order Qty > 2

Total Sales by Sale's Unit Price with Order Qty > 2 = 
CALCULATE(
    SUMX(Sales, Sales[OrderQty] * Sales[UnitPrice]),
    Sales[OrderQty] > 2
)

Scenario 1: Total Sales by Sale's Unit Price with Order Qty > 2 measure is the only field used in the visual. What will be the result?

Did table expansion happen? As there are no filters from the visual, the filter context is empty. CALCULATE creates a new filter context within which SUMX() will be evaluated. This new filter context is populated with one filter parameter Sales[OrderQty] > 2. As there is no reference to any field from the Product table, Sales table is not expanded. So, storage engine executes only one query on the Sales table.

SET DC_KIND="AUTO";
WITH
	$Expr0 := ( PFCAST ( 'Sales'[OrderQty] AS  INT ) * PFCAST ( 'Sales'[UnitPrice] AS  INT )  ) 
SELECT
SUM ( @$Expr0 )
FROM 'Sales'
WHERE
	 ( PFCASTCOALESCE ( 'Sales'[OrderQty] AS  INT ) > COALESCE ( 2 )  ) ;

Scenario 2: Product[Color] field and Total Sales by Sale's Unit Price with Order Qty > 2 measure are used in the visual. What will be the result?

Did table expansion happen? For each row in the table visual, the filter context contains a different value for the Product[Color]. CALCULATE creates a new filter context within which SUMX() will be evaluated. This new filter context is populated with two filter parameters: Sales[OrderQty] > 2 and Product[Color] from the original filter context. Since Product[Color] is on the one side of the relationship, storage engine expands the Sales table by executing a single left outer join query between Sales table and Product table [line 7, 8] to fetch the total sales by product color with Sales[OrderQty] greater than 2 [line 10].

SET DC_KIND="AUTO";
WITH
	$Expr0 := ( PFCAST ( 'Sales'[OrderQty] AS  INT ) * PFCAST ( 'Sales'[UnitPrice] AS  INT )  ) 
SELECT
'Product'[Color],
SUM ( @$Expr0 )
FROM 'Sales'
	LEFT OUTER JOIN 'Product' ON 'Sales'[ProductID]='Product'[ProductID]
WHERE
	 ( PFCASTCOALESCE ( 'Sales'[OrderQty] AS  INT ) > COALESCE ( 2 )  ) ;

Measure #3 Total Sales by Sale’s Unit Price with Order Qty > 2 (Filter function)

Total Sales by Sale's Unit Price with Order Qty > 2 (FILTER function) = 
CALCULATE(
    SUMX(Sales, Sales[OrderQty] * Sales[UnitPrice]),
    FILTER(
        Sales,
        Sales[OrderQty] > 2
    )
)

Scenario 1: Total Sales by Sale's Unit Price with Order Qty > 2 (Filter function) measure is the only field used in the visual. What will be the result?

Did table expansion happen? As there are no filters from the visual, the filter context is empty. As the filter context is empty, FILTER() doesn’t execute query on the storage engine rather Sales[OrderQty] > 2 is added to a new filter context within which SUMX() will be evaluated. This new filter context is populated with one filter parameter Sales[OrderQty] > 2. As there is no reference to any field from the Product table, Sales table is not expanded. So, storage engine executes only one query on the Sales table.

SET DC_KIND="AUTO";
WITH
	$Expr0 := ( PFCAST ( 'Sales'[OrderQty] AS  INT ) * PFCAST ( 'Sales'[UnitPrice] AS  INT )  ) 
SELECT
SUM ( @$Expr0 )
FROM 'Sales'
WHERE
	 ( PFCASTCOALESCE ( 'Sales'[OrderQty] AS  INT ) > COALESCE ( 2 )  ) ;

Scenario 2: Product[Color] field and Total Sales by Sale's Unit Price with Order Qty > 2 (Filter function) measure are used in the visual. What will be the result?

Did table expansion happen? Table expansion happens twice.

At the time of calling the measure, the filter context is not empty. It includes Product[Color]. As the filter context is not empty, FILTER() expands the Sales table to select Sales[OrderQty] and Product[Color] with order quantity greater than two.

SET DC_KIND="AUTO";
SELECT
'Product'[Color], 'Sales'[OrderQty]
FROM 'Sales'
	LEFT OUTER JOIN 'Product' ON 'Sales'[ProductID]='Product'[ProductID]
WHERE
	 ( PFCASTCOALESCE ( 'Sales'[OrderQty] AS  INT ) > COALESCE ( 2 )  ) ;

Then, CALCULATE creates a new filter context within which SUMX() will be evaluated. This new filter context is populated with two filter parameters: Sales[OrderQty] > 2 and Product[Color] from the result returned by FILTER(). Since Product[Color] is on the one side of the relationship, storage engine expands the Sales table again by executing the second left outer join query between Sales table and Product table [line 7, 8] to fetch the total sales by product color with Sales[OrderQty] greater than two [line 10].

SET DC_KIND="AUTO";
WITH
	$Expr0 := ( PFCAST ( 'Sales'[OrderQty] AS  INT ) * PFCAST ( 'Sales'[UnitPrice] AS  INT )  ) 
SELECT
'Product'[Color], 'Sales'[OrderQty],
SUM ( @$Expr0 )
FROM 'Sales'
	LEFT OUTER JOIN 'Product' ON 'Sales'[ProductID]='Product'[ProductID]
WHERE
	 ( PFCASTCOALESCE ( 'Sales'[OrderQty] AS  INT ) > COALESCE ( 2 )  ) ;

Conclusion:

When the filter context is empty, FILTER() doesn’t expand the Sales table otherwise it expands Sales table if FILTER() is called on a table. As we don’t have control on whether the filter context will be empty or not, we have two choices to avoid expanding Sales

  1. Use VALUES() in the table expression of FILTER().
  2. Don’t use FILTER().
CALCULATE(
    SUMX(Sales, Sales[OrderQty] * Sales[UnitPrice]),
    FILTER(
        VALUES(Sales[OrderQty]),
        Sales[OrderQty] > 2
    )
)

- or -

CALCULATE(
    SUMX(Sales, Sales[OrderQty] * Sales[UnitPrice]),
    Sales[OrderQty] > 2
)
Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s