CODEWITHSUNDEEP
powerbidax
variable
variable

Reputation: 9714

Why is it not recommended to use a table filter and instead use a multi-column filter in this example?

Link: https://www.sqlbi.com/articles/specifying-multiple-filter-conditions-in-calculate/

I am reading this article about a new DAX feature added to CALCULATE, that allows referencing multiple columns in the same predicate. Example:

Red or Contoso Sales :=
CALCULATE (
    [Sales Amount],
    'Product'[Color] = "Red" || 'Product'[Brand] = "Contoso"
)

This question is not about the above concept, but about the quote below. Specifically - Why is the author saying that using table filter is a common error? What is the problem with using table filter instead of a multi-column filter? I want an example to understand this.

Red or Contoso Sales := CALCULATE ( [Sales Amount], FILTER ( ALL ( 'Product'[Color], 'Product'[Brand] ), 'Product'[Color] = "Red" || 'Product'[Brand] = "Contoso" ) )

A common error is to use a table filter instead of a multi-column filter. For example, the Big Sales Amount measure or the initial example is often written in this sub-optimal manner:

Big Sales Amount := CALCULATE ( [Sales Amount], FILTER ( Sales, Sales[Quantity] * Sales[Net Price] > 1000 ) )

Upvotes: 1

Views: 272

Answers (1)

Jon
Jon

Reputation: 4967

When you create a filter clause, the DAX engine creates a table. So in this case

Big Sales Amount := CALCULATE ( [Sales Amount], FILTER ( Sales, Sales[Quantity] * Sales[Net Price] > 1000 ) )

It is loading the table 'Sales' completely in the background, then applying the formula on two columns in that table. If it is a wide table in terms of the number of columns, this will use up memory with irrelevant columns needed for the calculation. Marco Russo calls this a 'slow' pattern.

So it would be better to create a measure that only uses the two columns needed, so the filter creates a table in the background that is the minimum for use. So the above could be done as

Big Sales Amount := CALCULATE ( [Sales Amount], FILTER ( ALL ( Sales[Quantity], Sales[Net Price] ) * Sales[Net Price] > 1000 ) )

So the multi-column version is a lot lower in the memory overhead (called a 'fast' pattern) as it is only using the two columns needed, but as you use ALL you may need a KEEPFILTER in the formula to get the correct results. The DAX Engine has been updated and optimized so it will do this sort of table to column reduction without declaring it explicitly.

Upvotes: 1

Related Questions