Forum Discussion
Difference between CALCULATE(..., <clause>) vs CALCULATE(..., FILTER(..., <clause>))
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
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