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

New employee in the team!

After a long search, we are very happy to announce that we have hired a new member and expanded our work team! Karlo will strengthen our Power Platform team in the part of business analysis, which includes the following technologies:PowerBI,PowerQuery,PowerPivot/Tabular. He developed his business knowledge in the pharmaceutical industry, and...

Read more

Novi zaposlenik u timu!

Nakon dugog traženja, s velikom radošću objavljujemo da smo zaposlili novog člana i proširili svoj radni tim! Karlo će pojačati naš Power Platform tim u dijelu poslovne analize što podrazumijeva sljedeće tehnologije:Power BI,PowerQuery,PowerPivot/Tabular. Poslovno znanje razvijao je u farmaceutskoj industriji, a uz pomoć našeg iskusnog tima koji je na okupu...

Read more

Gostujuće predavanje na Microsoft 365 konferenciji

Dana 07.11.2024. sudjelovali smo na konferenciji “Tech Event – Microsoft 365” u suradnji s M-San i Callidus grupom. Konferencija je održana u svrhu predstavljanja širokih mogućnosti koje Microsoft 365 platforma nudi u vidu digitalizacije poslovanja, optimiziranju i automatizaciji poslovnih procesa i upravljanju sigurnosnim pitanjima. Naš PowerBI konzultant i trener Krešimir,...

Read more

Participation in the Microsoft 365 conference

On 7th of November we participated in the conference “Tech Event – Microsoft 365” in cooperation with M-San and Callidus group. The conference was held in order to present the broad possibilities that the Microsoft 365 platform offers in the form of digitalization of business, optimization and automation of business...

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