Filter behaviour - FILTER function vs IN operator in Calculate

Hi Uniqueusername 

Just adding my two cents to the discussion to explain the difference between the two measures. 🙂

General comments:

1. Every filter argument provided to CALCULATE (i.e. the 2nd argument onwards) is a ultimately a table. You can provide either an explicit table expression or a so-called "boolean expression" which is a type of shorthand that is converted to a table expression.
2. Each filter argument overwrites existing filters on the corresponding columns by default (unless KEEPFILTERS is used). When writing DAX expressions with CALCULATE, care should be taken to decide whether the filter argument will result in a filter within or overwriting current filters.

1. Formula 1 - Using FILTER function

In this measure, the filter argument provided to CALCULATE is:

FILTER ( 'Product', 'Product'[ClassName] IN { "Deluxe", "Economy" } )

This is a table expression that:

1. Takes the table 'Product' in the existing filter context where the measure is being evaluated.
   (In fact the "expanded" 'Product' table is used, but that is not critical for the current discussion).
2. Iterates through this table row-by-row and selects those rows where the condition is TRUE, i.e. rows of 'Product' where ClassName is either "Deluxe" or "Economy".
3. The result is a table which contains all columns of the (expanded) 'Product' table. This is then treated as a filter, and replaces all existing filters on columns of the 'Product' table for the purpose of calculating the first argument of CALCULATE: [Contoso sales].

Because the starting point was the table 'Product' from the exising filter context, the filtered version of 'Product' must contain a subset of the rows visible in the existing filter context.

The measure effectively applies a filter containing all Products with ClassName = "Deluxe" or "Economy" within the existing filter context, and evaluates [Contoso sales] within that context.

Roughly speaking, the result is [Contoso sales] for "Deluxe" and "Economy" classes within the existing filter context. This value would be expected to be "smaller" than [Total sales] or [Contoso sales].

Note: I would not generally recommend using FILTER ( <table>, ... ) as an argument for CALCULATE. See this article (listed below as well).

2. Formula 2 - Using IN operator in the calculate filter argument

In this measure, the filter argument provided to CALCULATE is:

'Product'[ClassName] IN { "Deluxe" , "Economy" }

This is an example of a boolean expression, which evaluates to either TRUE or FALSE for every value of ClassName. The DAX engine translates this expression into this single-column table:

FILTER (
    ALL ( 'Product'[ClassName] ),
    'Product'[ClassName] IN { "Deluxe" , "Economy" }​
)

which can also be stated as:

TREATAS (
    { "Deluxe" , "Economy" },
    'Product'[ClassName]
)​

This table expression:

1. Constructs a single-column table containing all distinct values that existing in the column 'Product'[ClassName] ignoring all filters that might exist (due to the ALL function).
2. Filters this table to just include values which are equal to "Deluxe" or "Economy".
3. The result is a single-column table of 'Product'[ClassName] values with two rows, containing the two values "Deluxe" and "Economy" (assuming those two values exist in the 'Product'[ClassName] column..

Because the starting point was the table ALL ( 'Product'[ClassName] ), the resulting table does not depend on the existing filter context where the measure is being evaluated, so it will always have the same two rows (assuming those two values exist in the overall table).

The measure effectively applies a filter of ClassName = "Deluxe" or "Economy", overwriting any existing filters on ClassName, while retaining any other filters and evaluates [Contoso sales] within that context.

Roughly speaking, the result is [Contoso sales] for "Deluxe" and "Economy" classes ignoring any existing filter on ClassName. That's why this measure is the same for every row of the matrix with ClassName on the rows. This measure has the potential to go outside the existing filter context.

Here are some useful articles in this general area:

• Article on why it is generally preferable to apply filters to columns rather than tables. Includes discussion of expanded tables.
  https://www.sqlbi.com/articles/filter-columns-not-tables-in-dax/
• Using KEEPFILTERS as a means of retaining existing filters when applying a filter in CALCULATE.
  https://www.sqlbi.com/articles/using-keepfilters-in-dax/
• Discussion of expanded tables which I didn't cover above.
  https://www.sqlbi.com/articles/expanded-tables-in-dax/
  https://mdxdax.blogspot.com/2011/03/logic-behind-magic-of-dax-cross-table.html
  
  

All the best,

Owen