DAX Calculate function with and without FILTER

Question

What is the difference in results of CALCULATE function if we use it with and without FILTER function. Suppose we have those two measures:

Measure1 = CALCULATE([X], 'FactTable'[Color]="Red")

Measure2 = CALCULATE([X], FILTER('FactTable', 'FactTable'[Color]="Red")

Update

Is there a way to modify Measure2 by using other functions, such as ALL, or ALLSELECTED, so that it would return exactly the same results as Measure1?

Answer 1

Not only the results, but also the way of obtaining those results for both measures will not be the same.

I created two measures similar to your example to test this:

TestAvgNoFilter = CALCULATE([PrcAvg]; cal[ReadDate]=DATE(2018;05;23))
TestAvgFilter = CALCULATE([PrcAvg]; filter(cal; cal[ReadDate]=DATE(2018;05;23)))

When I simply throw both of them into pivot table without any additional fields or slicers, ofcourse both of them show the same result:

However:

1. Using FILTER has significant performance impact, which can be clearly seen looking at query plans and utilization of Storage Engine vs Formula Engine. It creates additional temporary table that it needs to "interact" with already existing filters coming from report/pivot table itself (rows, columns, slicers). You won't notice anything for simple average value in single cell, but if your [x] measure itself is complicated and there are many of those "initial" filters, the difference in calculation time can be huge.

2. FILTER retains and iteracts with initial filter context, while filter expression used directly in CALCULATE ignores it. See what happens, when I add ReadDate to the pivot table:

This is precisely why the measure without FILTER is faster: it doesn't care what dates are in columns - it already calculated one "true" value, while the measure with FILTER evaluates itself against initial filters for every row.

Results in both columns can be considered correct - it really all depends on interpretation and how you name the measures ;).

As a general rule I would suggest you don't use FILTER when you don't have to. Save it's power for when it's really needed.

Answer 2

The difference here is that CALCULATE allows simple filters which will replace the existing filter context. In your example, CALCULATE will compute the measure [X] using the existing filter context, except that it removes any existing filter context for FactTable[Color] and replaces it with FactTable[Color] = Red.

The FILTER function is an iterator, which means it steps through the table (passed in as its first argument) one row at a time and evaluations the expression (second argument) for each row. When you have a FILTER function inside of CALCULATE, it will combine the existing filter context with the results of the FILTER (instead of replacing it like a simple filter argument).

In general, you want to use simple filters whenever you have a choice as the computation will be more efficient. However, the FILTER function allows you to do much more complex filtering so it still highly useful in cases where simple filters aren't enough.

---

Further reading: FILTER() – When, Why, & How to Use It

Answer 3

The DAX syntax of the automatic FILTER function generated by DAX in place of a logical expression requires that you express a single column in the filter expression. Let's take this example -

Measure1 = CALCULATE([X], 'FactTable'[Color]="Red")

The syntax above is internally transformed in the following one, which you might write in an explicit way obtaining the same behavior from your DAX measure.

Measure1 = CALCULATE([X], FILTER(ALL('FactTable'[Color]), 'FactTable'[Color]="Red"))

So If you use last function in Measure2 value it will fetch the same result.

For further reference you can visit this link.

Answer 4

The Measures you have created will have same output as you are only passing one value to filter as color "Red".

But what if you want to pass range of values (like Date Range) to filter from, Measure1 wont work as it will not able to do that as it passes only single value to filter.

In Measure2 you can pass range of specific values by using FILTER function from a specific table and column to get the desired output.

Measure2 = CALCULATE([X], FILTER('FactTable', 'FactTable'[Color])

You can use FILTER to reduce the number of rows in the table that you are working with, and use only specific data in calculations.

You can find more details on FILTER function with example on how it works, here -

https://msdn.microsoft.com/en-us/query-bi/dax/filter-function-dax