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