DAX HANDBOOK
6. CALCULATE

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.

Introduction

CALCULATE/CALCULATETABLE are the most important functions in DAX, and the only ones that are capable of modifying the original filter context. CALCULATE has a few rules you need to follow, and they aren’t that hard to understand. The complexity comes when those rules interfere with each other or with the original filter context.

Original Filter Context

Original filter context is made from all the filters that are influencing the Measure at a certain point in a visual before the Measure starts with its evaluation. These filters can be either direct ones (plotted on the same visual), or cross-filtered through the interaction between 2 or more visuals.

Note: It is extremely important to understand the following example thoroughly. Without the comprehensive understanding of the way original filter context is being created it will be impossible to comprehend filter context modifications or row 2 filter context transition.

When debugging a Measure, always pick a specific cell (or point) in a visual, then check which filters are being applied to the selected cell. Those filters can be direct or indirect. If we focus on the cell with a red border, we can conclude the following.

There are 2 direct filters impacting this cell. Those are:

  • DimProduct[Color]=”Black”
  • DimProduct[CategoryName]=”Components”

Besides direct filters, there are also 2 filters that cross filter this visual, and they are coming from other visuals on the canvas. Those are:

  • DimCustomer[BusinessType]=”Warehouse”
  • DimDate[Year] =”2020”

For that selected cell in the visual, there are 4 filters applied to the cell before the calculation even starts. The calculation [SalesAmount] has the following formula:

SalesAmount = SUMX(FactSales,FactSales[UnitPrice]*FactSales[OrderQuantity])

If we observe the data model, we can see that the measure is using only columns from the FactSales table. But we can also see that all the dimension tables have a 1-* relationship with the FactTable, meaning filters applied to their columns also flow to the FactSales table and filter it before the calculation starts. This is the reason why, even though the calculation is the same for every cell in the visual, it returns different values for different cells. This is all the result of the different filters applied to cells that are forming the original filter context under which the calculation is being evaluated.

Why do you need CALCULATE?

Once you plot measures in a visual, they always fall under the influence of the original filter context. This is extremely useful since you don’t have to worry about changing the calculation for every possible combination of fields plotted on the visual. but this also poses a big issue in case you need to break free of a certain original context filter (e.g. if you wish to always show the grand total values for every field). Or if you wish to change the original filter context so that you could do a comparison between different filters. For those mentioned, and many more purposes, you need a way of modifying or ignoring the original filter context. This is where the CALCULATE/CALCULATETABLE function shine.

Because DAX is all about filtering the data model, every logic you wish to write with DAX has to include some kind of filter manipulation. This is what is meant by “Thinking in DAX terms”. This is the hardest part of DAX to grasp, and since it almost always includes filter context manipulation, it also makes CALCULATE the hardest DAX function to master. But if you pay attention to its basic concepts and order of evaluation, you will have a much easier time understanding and using it correctly in your measures.

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

Held PowerApps training for Žito d.d.

Last week we went to Osijek to hold a PowerApps training for Žito d.d. The training took place at the client’s office, lasting 2 days, with a total duration of 8 hours. The participants of the training have been using PowerApps in their business for some time, but they wanted...

Read more

Održana PowerApps edukacija za Žito d.d.

Prošli tjedan svratili smo do Osijeka kako bismo održali PowerApps edukaciju za Žito d.d. Edukacija se održala u 2 dana kod klijenta u uredu, u sveukupnom trajanju od 8 sati. Polaznici edukacije već neko vrijeme koriste PowerApps u svom poslovanju, no željeli su usavršiti svoje vještine u samostalnoj izradi aplikacija,...

Read more

This year’s first PowerApps training for TÜV NORD Adriatic

Last week we held this year’s first PowerApps training for our long-term clients from the TÜV NORD Adriatic company. It was a full-day education lasting 6 hours, led by our PowerApps expert and developer Kristian! The PowerApps platform is intended for the creation of customized business applications and enables the...

Read more

Prva ovogodišnja PowerApps edukacija za TÜV NORD Adriatic

Prošli tjedan održali smo prvu ovogodišnju PowerApps edukaciju za naše dugogodišnje klijente iz TÜV NORD Adriatic kompanije. Bila je to cjelodnevna edukacija u trajanju od 6 sati, koju je vodio naš PowerApps stručnjak i developer Kristian! PowerApps platforma namijenjena je izradi prilagođenih poslovnih aplikacija i omogućava brzi razvoj aplikacija koje...

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