DAX HANDBOOK
6.6 Crossfilter

If you wish to follow along, you can find PBIX/Excel files at the bottom of the article. 

Authors

Krešimir Ledinski

Krešimir Ledinski

Microsoft certified expert in the field of Business Intelligence. His biggest passions are DAX, M, and data modeling.

Kristian Radoš

Kristian Radoš

Experienced data analyst. Advanced in SQL, PowerApps and M language.

Crossfilter is a feature of DAX when it filters the underlying dataset even though there aren’t any visual filters present.

Introduction

In this example, we will explain a very important feature of CALCULATE filter arguments. We will also explain why you should always prefer the combination of ALL/REMOVEFILTER + VALUES instead of ALLEXCEPT function. We will use the following 2 measures and observe their results.

ALLEXCEPT_Color = CALCULATE([SalesAmount],ALLEXCEPT(DimProduct,DimProduct[Color]))
REMOVEFILTERS_VALUES_Color = CALCULATE([SalesAmount],REMOVEFILTERS(DimProduct),VALUES(DimProduct[Color]))

If we observe the calculation, we might assume that they both should return the same values. But we can see that they return different figures in the visual. Let’s focus on the Accessories row and see what each of these functions returns.

ALLEXCEPT Color: prior to calculation, this function removes all filters from the DimProduct table, except the one coming from the column DimProduct[Color].

REMOVEFILTERS+VALUES Color: This calculation does almost the same thing as the ALLEXCEPT version, but with one big difference. With the use of VALUES function, it activates the crossfilter feature of DAX. What is the crossfilter feature? The table below shows all colors being sold with the Accessories CategoryName. We can clearly see that there are only 5 colors present, while other colors (like the color Yellow in the Bikes segment) are not present.

Order of Evaluation

The calculation ignores all columns coming from the DimProduct table, but then reapply values that are valid for the DimProduct[Color] column. In doing so, it also crossfilters the DimProduct table. If we focus on the Accessories cell in the first picture, the crossfilter does the following:

  1. Before the calculation starts, the filter arguments of the CALCULATE are put in AND condition.
  2. The first filter argument ignores all columns from DimProduct table, including the CategoryName one.
  3. The second argument reinstalls the filter on the color column, which crossfilters the CategoryName. Even though the Color column is not in the visual, the crossfilter effect is visible because the value of 59 mil. is the sales amount of the Black, Blue, NA, Red, and Silver colors combined. Those are the colors with sales in the Accessories category. Sales amounts of other colors (Yellow, Grey, etc.) are ignored since there aren’t any sales of Accessories in those colors.
  4. The SalesAmount is calculated in the new context which, for accessories, consists of Black, Blue, NA, Red, and Silver colors.

If we expand the visual to a color granularity, then on the Color level both calculations return the same numbers, but on the top CategoryName level, the ALLEXCEPT returns an incorrect sum of the Color values.

In most calculations you would want to use the combination of REMOVEFILTERS+VALUES since it is more predictable and easier to use/understand. ALLEXCEPT is used in more advanced coding and only in specific situations.

Materials

We wish to create the best possible content!

If you are a novice looking for a better explanation of any element of the topic, feel free to comment on the part you didn't quite understand!

If you are an expert in DAX and believe certain topic lacks important internals, your comments are more than welcomed!

COMMENTS

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments

OUR SERVICES

Prefer live training or consultations?

Table of Content

Table of Content

GET LATEST BI NEWS TO YOUR INBOX

Receive the latest updates on all business analyst news across all platforms.

By subscribing you are agreeing to our Privacy Policy.

Related blog posts

DAX HANDBOOK
7. VARIABLES

Variables are used in almost every measure you will create. The reason we introduce them last is that they use all other parts of DAX code to produce faster, more powerful and maintainable code. Variables are like containers of a part of the DAX code which can be used throughout...

Read more

DAX HANDBOOK
6.8 ALLSELECTED

Explanation ALLSELECTED is one of the most complex functions in DAX. When used improperly it can lead to unexpected results. As a rule of thumb, you should not use it in iterative functions. It is preferable to use it only as a CALCULATE filter remover, not as a table function....

Read more

DAX HANDBOOK
6.7 Lineage

What is Lineage? Lineage is a part of DAX mechanics that enables us to use tables as filter arguments for the CALCULATE function. It’s also used in row2filter context transition and other aspects of the data model that involve filter propagation throughout the model. We can state that lineage is...

Read more

DAX HANDBOOK
6.6 Crossfilter

Crossfilter is a feature of DAX when it filters the underlying dataset even though there aren’t any visual filters present. Introduction In this example, we will explain a very important feature of CALCULATE filter arguments. We will also explain why you should always prefer the combination of ALL/REMOVEFILTER + VALUES...

Read more
0
Would love your thoughts, please comment.x
()
x
Scroll to Top