Filter behaviour - FILTER function vs IN operator in Calculate

[Uniqueusername]

Hi all,

Hope the brilliant community here can help me out with some very basic concepts in DAX !!

I am trying to understand the FILTER behaviour in a calculate function. Here are two formulas which are producing different results. I am using the contoso model ( not sure which version it is)

Formula 1 - Using FILTER function

Deluxe and Economy Contoso Sales (USING FILTER) = 
CALCULATE(
    [Contoso sales]
  // ,'Product'[ClassName]="Deluxe" || 'Product'[ClassName]="Economy"
   ,FILTER('Product','Product'[ClassName] IN {"Deluxe", "Economy"})
   //,'Product'[ClassName] IN { "Deluxe" , "Economy"}

)

The above formula is resulting in this

Formula 2 - Using IN operator in the calculate filter argument

Deluxe and Economy Contoso Sales (USING IN) = 
CALCULATE(
    [Contoso sales]
  // ,'Product'[ClassName]="Deluxe" || 'Product'[ClassName]="Economy"
 //  ,FILTER('Product','Product'[ClassName] IN {"Deluxe", "Economy"})
    ,'Product'[ClassName] IN { "Deluxe" , "Economy"}

)

The second one is resulting in this

What is the difference between the two ? Why is the first one not adding Deluxe + Economy ( which was what I was expecting)

Thanks in advance!!

[v-karpurapud]

Hi @Uniqueusername 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hi @Uniqueusername 

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and give a kudos, as this helps other members in  community.

Thank You!

[v-karpurapud]

Hi @Uniqueusername 

Could you please confirm if your query have been resolved the solution provided by @OwenAuger ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you

[OwenAuger]

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

[Uniqueusername]

Thanks @OwenAuger ! Worth way more than 2cents for sure. It will take some time for me to digest this

[Uniqueusername]

sorry, I still don't get how the filters are behaving in the two examples. The ALLSELECTED is not something I use regularly and from what I have read about it is quite a complex one

[lbendlin]

It is done for you automatically.  Generally it is a good thing but there are side effects.  You will want to invest in reading about it.

[lbendlin]

The second version automatically applies a FILTER(ALLSELECTED()) around your condition.