FILTER vs Boolean Expression

[pedromelato]

I have two similar measures that in my mind should yield the same output, but in practice it gives me different results. While one uses the FILTER function, the other uses a boolean expression to filter. Can an expert help me understand why?

First measure:

DevOps - Completed Tasks - Filter =

CALCULATE(

    DISTINCTCOUNT(factDevOpsTasks[Work Item Id]),

    FILTER(

        factDevOpsTasks,

        factDevOpsTasks[Work Item Type] = "Task"),

    USERELATIONSHIP(dimDate[Date], factDevOpsTasks[Completed Date]))

First output:

Second measure:

DevOps - Completed Tasks =

CALCULATE(

    DISTINCTCOUNT(factDevOpsTasks[Work Item Id]),

    factDevOpsTasks[Work Item Type] = "Task",

    USERELATIONSHIP(dimDate[Date], factDevOpsTasks[Completed Date]))

Second Output:

Thanks! 

[OwenAuger · Accepted solution]

Hi @pedromelato 

You've raised an interesting topic: "Table filters vs column filters" 🙂

Rough description of what's happening:

1. The first measure applies the Work Item Type = "Task" filter within the current filter context, so the DISTINCTCOUNT will be evaluated in a restricted filter context.
2. The second measure applies the Work Item Type = "Task" filter by overriding any existing filters on this column, so it can potentially result in a filter context outside the original filter context.

Comparison of the two filter arguments

Your two measures are identical expect for the second argument provided to CALCULATE, so let's focus on those arguments:

1. First measure:

FILTER (
    factDevOpsTasks,
    factDevOpsTasks[Work Item Type] = "Task"
)

Intuitive description of this filter:

Start with the factDevOpsTasks table visible within the current filter context, and limit it to rows where Work Item Type = "Task. This will produce a filter "within" the original filter context.

Detailed description:

This expression refers to the entire factDevOpsTasks table evaluated within the current filter context, filtered to those rows where Work Item Type = "Task". 

In fact, when a (possibly filtered) physical table is provided to CALCULATE as a Filter argument, it is treated as the "expanded" version of that table. The expanded table is defined as the table itself joined to the related tables that can be reached by following many-to-one relationships beginning with that table. This is similar to joining the related tables through left outer joins.

In your example, the expanded version of factDevOpsTasks includes columns of this table itself, plus columns of any related tables on the one-side of many-to-one relationship (continuing recursively along many-to-one relationships). The rows of this table are those visible in the current filter context, limited to those with Work Item Type = "Task". 

2. Second measure:

factDevOpsTasks[Work Item Type] = "Task"

Intuitive description of this filter:

Applying a single column filter Work Item Type = "Task" overriding any filters on that column that might exist.

Detailed description:

This expression is automatically translated to:

FILTER (
    ALL ( factDevOpsTasks[Work Item Type] ),
    factDevOpsTasks[Work Item Type] = "Task"
)

This results in a single-column table containing the single value Work Item Type = "Task".

Unlike the argument used in the first measure, this Filter argument impacts only one column. It will also override any existing filters that might exist on that column, due to the use of ALL(...). This has the potential of expanding beyond the original filter context.

Recommendation

1. It is best to apply column filters rather than table filters. Table filters (as in your first measure) can produce unpredictable results due to table expansion.
2. If you need to retain existing filters, wrap the filter in KEEPFILTERS.

So if you want to apply a filter Work Item Type = "Task" within the current filter context, use:

KEEPFILTERS ( factDevOpsTasks[Work Item Type] = "Task" )

But if you want to apply the same filter except allow existing filters to be overridden, use:

factDevOpsTasks[Work Item Type] = "Task"

References

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

https://www.sqlbi.com/articles/expanded-tables-in-dax/

https://www.sqlbi.com/articles/introducing-calculate-in-dax/

YouTube: CALCULATE in DAX #04: Add table filter (and difference with column filters)

Hope that helps!

Regards

[pedromelato]

That is an awesome answer, very clarifying. Thank you so much!