Take a Deep Dive into Filtering in DAX

We at all times use filters when growing DAX expressions, corresponding to DAX measures, or when writing DAX queries.

However what occurs precisely once we apply filters?

This piece is precisely about this query.

I’ll begin with easy queries and add variants to discover what occurs below the hood.

I take advantage of DAX Studio and the choice to indicate server timings for every question.

In case you need to be taught extra about this characteristic and learn how to interpret the outcomes, learn the primary article within the References part on the finish of this piece.

Let’s begin with the bottom question:

EVALUATE
	CALCULATETABLE(
				SUMMARIZECOLUMNS('Product'[BrandName]
						,"On-line Gross sales", [Sum Online Sales]
						)
					)

After we activate the server timings and execute the question, we get the execution statistics and the Storage Engines (SE) question/queries wanted to get the information:

As you’ll be able to see, we’d like just one Storage Engine (SE) question to retrieve the outcomes.

The question completes in solely 47 ms and is served nearly totally by the SE (95.7%).

The extra time the SE can spend on a question, the higher, as a result of it’s the part that retrieves knowledge from the information shops and tables.
Furthermore, the SE can use a number of CPU cores, whereas the Method Engine (FE) can use just one. We can’t study precisely what occurs within the FE as simply as we are able to with SE queries.

You’ll be able to be taught extra in regards to the distinction between these two engines within the article talked about above.

A brief notice:

A couple of months in the past, I wrote an article right here with a really comparable title. However, whereas that one was solely about date filters with Time Intelligence features, this one goes one step deeper into the rabbit gap.

That is far more generic than that one.

Should you missed it, I added the article hyperlink and extra sources on the present matter to the References part under.

Add easy filters

Subsequent, we add a easy filter for the product colour purple to the question:

EVALUATE
	CALCULATETABLE(
				SUMMARIZECOLUMNS('Product'[BrandName]
						,"On-line Gross sales", [Sum Online Sales]
						)
				,'Product'[ColorName] = "Crimson"
				)

Right here is the question and the outcomes restricted to the product colour purple:

After we have a look at the question statistics, we see this:

As you’ll be able to see, all the question is executed in a single SE question.

The filter is within the question’s WHERE clause. Subsequently, solely the restricted knowledge is retrieved.

That is seen within the “Rows” column, as solely 14 rows are returned from this question.

However what occurs once we use the FILTER() operate to filter the merchandise:

EVALUATE
	CALCULATETABLE(
				SUMMARIZECOLUMNS('Product'[BrandName]
						,"On-line Gross sales", [Sum Online Sales]
						)
				,FILTER('Product'
						,'Product'[ColorName]  = "Crimson")
				)

As you would possibly know, utilizing the FILTER() operate will not be advisable as a result of the way it works.

You’ll be able to be taught extra about this matter within the second article linked within the References part under.

The outcome doesn’t change:

However how does it have an effect on the execution plan and the SE queries?

As you’ll be able to see, on this case, the SE optimizes the question, yielding the identical execution plan as earlier than.

However, as we alter our code, we’ll see that utilizing FILTER() isn’t at all times a good suggestion.

Add a number of filters

Now, what occurs once we add a number of filters to a question?

EVALUATE
    CALCULATETABLE(
                SUMMARIZECOLUMNS('Product'[BrandName]
                        ,"On-line Gross sales", [Sum Online Sales]
                        )
                ,'Product'[ColorName]  = "Crimson"
                ,'Geography'[ContinentName] = "Europe"
                )

Whereas the outcome will not be that attention-grabbing to us, let’s have a look at the question statistics:

Once more, the question will be served by a single SE question that comprises each filters.

The question executes so shortly that the FE time share is comparatively excessive, but it nonetheless solely takes 6ms.

When altering the question to make use of the FILTER() operate, the SE question doesn’t change both:

This exhibits that, with this sort of question, the engine can optimize execution to search out probably the most environment friendly technique to fulfill the DAX question.

Anyway, the outcome doesn’t change. It’s equivalent in each instances, appropriately, as a result of we don’t change the filter per se. However please be affected person with me; I’m getting again to the FILTER() operate and why it’s necessary to grasp its results in a second.

Transferring filters into measures

Subsequent, let’s see what occurs when the filter is moved into the measure.

Till now, the question was constructed in order that the measure [Sum Online Sales] obtained its filter from outdoors.

Let’s do this:

DEFINE 
MEASURE 'All Measures'[Online Sales A. Datum] =
		CALCULATE(
			SUMX('On-line Gross sales', ( 'On-line Gross sales'[UnitPrice] * 'On-line Gross sales'[SalesQuantity]) - 'On-line Gross sales'[DiscountAmount] )
			,'Product'[BrandName] = "A. Datum"
			)


EVALUATE
	CALCULATETABLE(
				SUMMARIZECOLUMNS('Product'[BrandName]
						,"On-line Gross sales A. Datum", [Online Sales A. Datum]
						)
				)

As you’ll be able to see, the filter is utilized contained in the measure [Online Sales A. Datum].

In fact, the ensuing quantity is similar in every row of the outcome, because the Model is ready as “A. Datum”:

However the execution is barely totally different:

This time, we’ve two SE queries.

1. The question to get the gross sales for the Model “A. Datum”. This question comprises the filter for that model.
2. The second question is used to get the record for all manufacturers within the outcome set.

The primary question is most necessary to us, as a result of it nonetheless exhibits the filter for the model set throughout the measure.

This question will be absolutely served by the SE with a easy filter in a really environment friendly manner.

However, most often, we need to add a number of measures to a question (or a visible in a report).

What occurs once we add the [Sum Online Sales] measure to the question?

The outcome will not be notably necessary, because it exhibits one column with gross sales for every model and one other with gross sales for the filtered model.

However the question statistics are attention-grabbing:

As you’ll be able to see within the red-marked line within the SE question, the Model filter is not current.

As a result of the engine acknowledges that the filter within the measure is utilized to the identical column because the one within the question, it strikes the filter to the FE and returns the outcome.

Now, what occurs once we filter one other column within the measure, for instance, the colour:

DEFINE 
MEASURE 'All Measures'[Online Sales Red] =
        CALCULATE(
            SUMX('On-line Gross sales', ( 'On-line Gross sales'[UnitPrice] * 'On-line Gross sales'[SalesQuantity]) - 'On-line Gross sales'[DiscountAmount] )
            ,'Product'[ColorName] = "Crimson"
            )


EVALUATE
    CALCULATETABLE(
                SUMMARIZECOLUMNS('Product'[BrandName]
                        ,"On-line Gross sales", [Sum Online Sales]
                        ,"On-line Gross sales Crimson", [Online Sales Red]
                        )
                )

Once more, the outcome will not be notably attention-grabbing. We have an interest within the question statistics:

As you’ll be able to see, this time we’ve two queries by BrandName. One with out and one with the filter for the colour.

Each queries return the identical variety of rows (14) – one for every Model.

The FE handles combining the 2 outcomes right into a single desk.

Your complete question remains to be served primarily by the SE, which is great.

However now, let’s add the FILTER() operate to the Filter:

For this instance, I alter the measure to filter for 2 values with the IN operator:

,'Product'[BrandName] IN { "A. Datum", "Journey Works" }

On this variant, the SE question is like those earlier than.

The filter is handed instantly into the question’s WHERE clause.

However what occurs after I change it to this:

,FILTER('Product'

       ,'Product'[BrandName] IN { "A. Datum", "Journey Works" }

       )

To begin with, the outcome adjustments:

The reason being that FILTER() works fully in another way.

It retains the present filter context and provides a brand new one.

I defined this habits in one other article that I added because the second hyperlink within the References part under.

Furthermore, the SE can’t deal with this in a single question anymore:

The primary two queries retrieve the values for the model to filter (See the queries marked in pink).

Discover the big variety of rows (324 and a couple of’560) returned by the primary two queries. That is the materialization of intermediate outcomes wanted to carry out the calculation.

The third question makes use of these intermediate outcomes to filter the information (marked in purple).

The results of the third question is simply two rows—the 2 rows we see within the total outcome.

As described in my different article, FILTER() should be used with care.

Not solely is it significantly slower, however it additionally works fully in another way from a easy filter.

Anyway, I can restore the earlier habits by including an ALL() within the FILTER() name:

I don’t need to disguise the truth that this instance is particular, because the filter utilized impacts the identical column as used within the question.

When altering the question to filter the nation, the engine can optimize the execution and use the easy kind once more:

As you’ll be able to see, the engine optimizes the execution of the question and falls again to a easy filter when filtering columns that differ from these used within the DAX question. Within the blue inset, you see the outcomes.

I see this type of filtering fairly often when builders who will not be as proficient write DAX measures.

Utilizing the FILTER() operate appears to be like intuitive, however it could possibly yield incorrect or complicated outcomes and is slower than a easy filter. I strongly advocate studying my article linked under about this operate, in addition to the dax.guide documentation and the articles linked on SQLBI.com.

Moreover, I’ve to kind far more than when utilizing a easy filter.

As a lazy man, this is a vital cause to not use FILTER() when it’s pointless.

Add a fancy filter

Lastly, I need to present what occurs when making use of a filter utilizing a DAX operate, corresponding to CONTAINSSTRING().

EVALUATE
	CALCULATETABLE(
				SUMMARIZECOLUMNS('Product'[BrandName]
						,"On-line Gross sales", [Sum Online Sales]
						)
				,CONTAINSSTRING('On-line Gross sales'[SalesOrderNumber], "202402252C")
				)

Such a question is executed if you use a slicer in your report back to filter for a selected order and retrieve the manufacturers of the bought merchandise.

Because the outcome will not be necessary at this level, let’s instantly have a look at the question statistics:

Whereas the question took greater than 6 seconds to finish, 99.6% of the time was spent by the FE executing the CONTAINSSTRING() operate to search out matching rows within the knowledge. This operation may be very CPU-intensive, because the FE can use just one core. After I execute this question on my laptop computer, it takes greater than 2 seconds longer.

I intentionally selected a sluggish operate to show its results.

However the SE was nonetheless capable of execute the question with a single question. Nevertheless, the constructive impact of this reality is negligible on this case.

Conclusion

Whereas it isn’t my intention to offer you recommendation on what to do and what to not do, I needed to indicate you the results of the alternative ways to write down DAX code and apply filters in your measures or queries.

The DAX engine(s) are very environment friendly in optimizing the queries, however they’ve limitations.

Subsequently, we should at all times take care when writing our DAX code.

If the efficiency is poor or the code written by another person appears to be like unusual, we must always analyze it to find out learn how to enhance it.

I needed to indicate you learn how to do it and what to search for when analyzing your DAX code.

Bear in mind:

• The Storage engine (SE) can use a number of CPU cores.
• The extra work is completed by the SE, the higher.
• The SE can execute solely easy aggregations and simple arithmetic features (like +, -, x, and /)
• Attempt to scale back the workload on the Method Engine (FE)
• The FE can use just one CPU core.
• Attempt to scale back the materialization of information (The Rows column within the question statistics).
• Attempt to scale back the variety of SE queries.

I do know that the necessities will power us to write down DAX code, which isn’t optimum.

Even worse, the Report designers would possibly add logic to the report that causes a poor efficiency.

In such instances, get rid of that logic and verify the response time once more. It is likely to be value exploring making a devoted measure for such instances. Keep in mind that it’s doable to create native measures in a report that’s linked to a Semantic mannequin through a life connection.

However most significantly: Take your time when writing DAX code. You would possibly save time by avoiding the necessity to optimize your DAX code, which was written in a rush. I converse from expertise. This can be a very unhealthy feeling.

I hope you discovered one thing new.

References

To be taught the small print about learn how to interpret the outcomes of the Server Timings in DAX Studio, learn this piece: