Difference between CALCULATE(..., <clause>) vs CALCULATE(..., FILTER(..., <clause>))

[DarylK_MA]

Hi Everyone,

Looking to verify my understanding on how CALCULATE and FILTER functions work. In this dummy example, I have 2 tables with a single direction filter.

When I attempt to perform a DAX computation (I.e. DISTINCTCOUNT(Country[Country])) it seems that wrapping up the clause in FILTER(...) creates an alternative filtering direction. Examples below:

V3's computation is similar to the example in MS doc's for CROSSFILTER: https://learn.microsoft.com/en-us/dax/crossfilter-function-dax 

TLDR: From my understanding, since a filtering direction from sales to country is not enabled, both measures V1 and V2 should return 4; however, V2 returns 1. On the other hand, V3 enforces cross-filtering from sales to country, thus giving the answer 1 as expected.

My understanding of how CALCULATE and FILTER might be wrong and I would appreciate if anyone could offer some inputs 😀 

[Poojara_D12]

Hi @DarylK_MA 

It sounds like you have a good grasp on the concepts! CALCULATE and FILTER can indeed behave a bit differently when it comes to cross-filtering directions, especially when you have a single-direction relationship in your model.
Here in the above case, 

• V1: Returns 4 because there's no enforced cross-filtering direction, so only the initial filtering from the Country table is considered.
• V2: Returns 1 because using FILTER within CALCULATE indirectly introduces a temporary cross-filtering effect.
• V3: Explicitly enforces bidirectional filtering using CROSSFILTER, yielding 1 as well.
  So what I think is,
  • Using FILTER within CALCULATE can implicitly create row context transitions and alternative cross-filtering.
  • For precise control, use CROSSFILTER when you need a bidirectional filter for specific calculations, rather than relying on implicit behavior from FILTER.

  Did I answer your question? Mark my post as a solution, this will help others!

  If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

  Kind Regards,
  Poojara
  Data Analyst | MSBI Developer | Power BI Consultant

[OwenAuger · Accepted solution]

Hi @DarylK_MA 

You've raised a good question 🙂

The immediate answer to the question of why V2 appears to apply a filter from Sales to Country (i.e. the opposite of the relationship crossfilter direction) is that when physical model tables are provided as filter arguments within CALCULATE (whether or not enclosed in FILTER or CALCULATETABLE), they are treated as expanded tables which include all columns of tables on the 1-side of many-to-1 relationships from the table referenced.

In your example, when FILTER ( Sales, ... ) is included as a filter argument within CALCULATE, the filtered expanded Sales table includes all columns of related tables including Country (and any others, e.g. 'Date'). This often leads to unexpected results, since the outcome depends on all tables related to the table referenced.

Your V1 & V3 measures, however, apply filters on columns, so the behaviour is as you would expect. A filter on a single column cannot be converted into an expanded table.

Because of this behaviour with expanded tables, this article recommends filtering columns, not tables (and I would concur as a general rule):

https://www.sqlbi.com/articles/filter-columns-not-tables-in-dax/

Here are some other articles I suggest reading on this subject:

https://mdxdax.blogspot.com/2011/03/logic-behind-magic-of-dax-cross-table.html (classic post from Jeffrey Wang)

https://www.sqlbi.com/articles/filter-arguments-in-calculate/

This article on Related Distinct Count may also be of interest:

https://www.daxpatterns.com/related-distinct-count/

Regards