The CALCULATE() Function – Theory

Locked lesson.

Upgrade

About this lesson

CALCULATE() is the super-charged SUM(anything) function, and mastering it is the secret to truly mastering Power BI's formula language. In this module we will show how it works, as well as how it impacts basic measure calculation.

Exercise files

There are no related exercise files for this lesson.

Quick reference

The CALCULATE() Function – Theory

Understanding CALCULATE(), the most powerful function in DAX.

When to use

When you need to create or debug a CALCULATE() function to get the correct results.

Instructions

Key concepts around measure calculation:

  • Every measure result is evaluated independently
  • Each measure starts with the raw and unfiltered data tables
  • The filter context is calculated normally
  • Filters declared inside the CALCULATE() function are then applied, adjusting the filter context
    • Filters that are not already in the filter context are added
    • Filters that are already in the filter context are overridden
  • The revised filter context is then applied to the source tables, reducing the number of rows to use in the calculation
  • The Measure’s arithmetic signature is applied to whatever rows are left

What contributes to Filter Context?

  • The intersection of any fields used in the current visual (each data point is a cross section that inherits the rows/columns/axis/legend, etc..)
  • Any selected data points in other visuals
  • Selections made in any (all) slicers
  • Page or report level filter selections
  • Filters declared in the CALCULATE() function

Hints & tips

  •  AND filters can be used inside CALCULATE() using a double & character:
    • Lunch Sales = CALCULATE([Sales],Sales[Hour]>7&&Sales[Hour]<=11)
  • Or filters can be used inside CALCULATE() using a double | character:
    • Open Tickets = CALCULATE([Ticket Count],Tickets[Status]=”Open”||Ticket[Status]-“Pending”)
  • If you are trying to compare more complex operations you may need to nest a FILTER() function in CALCULATE’s filters area
  • Watch your data types… if your years are numeric, you need to compare against a number.  If the data type is text, you’ll need to wrap the value to compare in quotes

 

Login to download
  • 00:04 Power BI has a lot of functions, but by far the most magical one is CALCULATE().
  • 00:11 So what is CALCULATE() anyway?
  • 00:14 Well, if you think back to Excel, we have functions like sum if,
  • 00:18 which allows you to sum something if specific criteria are met.
  • 00:23 CALCULATE() is essentially an <any function>IF type function.
  • 00:27 It could be SUMIF, or COUNTIF or
  • 00:29 even Standard Deviation IF which is something that doesn't exist in Excel but
  • 00:34 you can build it inside Power BI, which is really cool.
  • 00:39 The syntax for this particular function works like this.
  • 00:42 It starts with =CALCULATE and then it asks for an expression,
  • 00:47 followed by a list of filters that you want to apply.
  • 00:51 Now when we actually look at this, we say, okay, well what does that mean,
  • 00:55 expression?
  • 00:56 Well, expression is essentially just either an aggregation function like
  • 01:00 sum of transactions amount or, more commonly, it's a measure.
  • 01:05 So we could put in something that says calculate sales
  • 01:08 where the sales year equals 2016.
  • 01:10 So, this allows us to start actually rolling on some
  • 01:14 more complicated logic into our measures to return specific pieces.
  • 01:19 You also have the ability to do things more like dynamic filtering where we
  • 01:23 want to see if things are greater than or equal to a value in another column.
  • 01:28 Those get really complicated and I'm not gonna go through those but
  • 01:30 what I will tell you is if you're trying to do something like that you need to
  • 01:34 use a command called FILTER().
  • 01:36 FILTER() gets nested into the filters area, with its own expressions, but
  • 01:41 it's outside the scope of this course.
  • 01:43 This is a much more advanced formula and if you're looking for
  • 01:45 that you're looking for training in something called DAX.
  • 01:48 DAX is called data analysis expressions.
  • 01:51 It is the language that all of our formulas in Power BI
  • 01:54 are actually written in, but
  • 01:55 again it's outside the scope of what we want to cover in this particular course.
  • 01:59 So it tends to get much more advanced.
  • 02:02 Now I want to explore how CALCULATE() actually works.
  • 02:05 How does a measure that uses CALCULATE() get calculated?
  • 02:09 It's actually very similar to the way that a normal measure calculates.
  • 02:13 Where it actually works out to the raw, unfiltered data tables,
  • 02:16 works out the filter context, and applies those to the underlying data source.
  • 02:19 And returns the arithmetic for whatever's left.
  • 02:21 But with CALCULATE() there's one added step.
  • 02:25 Once again, we're going to go back to this little table here, which has our
  • 02:27 temperature ranges down the left, we have them on the short column on the top.
  • 02:31 We've got a value in the middle.
  • 02:33 But this time, it uses a measure pattern that looks like this.
  • 02:36 2009 Sales = CALCULATE.
  • 02:38 What's the Actual?
  • 02:39 Well, the Actual is the average of this particular column.
  • 02:43 You'll notice that there's some filters declared, Calendar[Year]=2009.
  • 04:50 Arithmetic pattern on and return that to the visual.
  • 04:53 And then, when we go on to the next cell we start all over again from the raw
  • 04:56 unfiltered tables, work out the filter context, modify the filter context.
  • 05:00 Apply it, reduce the number of rows in the table, perform the arithmetic, and return
  • 05:04 it to the visual for each individual cell in the visual that needs to be returned.

Lesson notes are only available for subscribers.

Gift this course