Locked lesson.
About this lesson
Creating measures is all very well, but you need to understand why they return what they do. In this module we will explain how to identify the filter context applicable to your data point, and how that influences the way measures are calculated.
Exercise files
There are no related exercise files for this lesson.
Quick reference
Understanding Measure Calculation
Understanding how the DAX engine calculates measure results.
When to use
When you need to clarify why a value is being returned as it is, or when you need to debug a result.
Instructions
Key concepts around measure calculation:
- Every measure result is evaluated independently
- Each measure starts with the raw and unfiltered data tables
- Filters are applied to the underlying tables based on the “Filter Context”, 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
Hints & tips
- If the measure contains a CALCULATE() function, this debugging chart will not be sufficient for your needs. Please see the module on Understanding CALCULATE().
- 00:04 As cool as it is to be able to write a measure inside Power BI,
- 00:08 it's more important to actually understand how these measures are actually evaluated.
- 00:15 Every single measure, every single data point that you see on a visual,
- 00:19 it's important to understand is evaluated against the raw source tables.
- 00:24 And this is a key thing to recognize.
- 00:27 Every single data point is evaluated independently of
- 00:30 all of the others that you see on the visual.
- 00:33 What this means is, if you have a table that shows a total on row one, and
- 00:37 a total on row two, and down the bottom, it's got a grand total,
- 00:41 that grand total is not actually the sum of the things above.
- 00:45 It reaches back to the raw table,
- 00:47 and is evaluated independently of the items above it in the visual.
- 00:51 And that's a little bit of a confusing concept to understand.
- 00:54 And that's why we're really gonna focus here on how these measures actually
- 00:58 calculate.
- 01:00 The way that it's done is that every single data point
- 01:04 actually reaches back into the raw source table for the model.
- 01:09 It then applies something called the filter context to those tables.
- 01:13 And basically what this means is it looks at all of the other visuals and
- 01:17 what's been filtered upon.
- 01:18 So if you selected a specific thing in another visual that becomes part of
- 01:22 the filter context.
- 01:23 That filter context generally reduces the number of
- 01:26 rows that are available in the raw source table,
- 01:28 cuz it says, hey these ones are no longer relevant, let's get rid of them.
- 01:33 Once it's finished applying the filtered context and
- 01:35 getting rid of the rows that are no longer relevant,
- 01:37 it then performs the arithmetic that's in your measure pattern, the sum function or
- 01:42 count function whatever you wrote on whatever rows are left.
- 01:46 It returns that aggregation to the actual visual for that specific data point.
- 01:52 And then it moves on to the next data point and it starts all over again.
- 01:57 This means that understanding and
- 01:59 controlling filter context is the key to mastery of formulas inside Power BI.
- 02:05 So this is one of the reasons why we're really focusing on
- 02:08 this particular piece right here, and
- 02:10 why we're gonna drill it home with one more really detailed example here.
- 02:15 So let's look at figuring out what filter context is, and how it actually applies?
- 02:20 We're gonna do that in the context of this particular table.
- 02:23 Notice we've got temperature range down the left hand side, we have a column
- 02:27 called July, and then the body area of the table, we have some values.
- 02:31 Those values are based on the measure pattern called Avg Daily Sales,
- 02:36 which is equal to the average of sales amount.
- 02:39 So we're taking the sales amount column, were averaging
- 02:43 all of the values that are applicable, and we're returning it into a specific cell.
- 02:48 Specifically right now, we're gonna focus on this one here that shows 177.13,
- 02:53 how do we actually come to that number?
- 02:56 Well, as it happens, there's a couple of more things in play here, too.
- 02:59 We have a slicer.
- 03:01 Now a slicer is a very special kind of visual that layers
- 03:04 on some more filter context.
- 03:06 It's one of the very odd ones that can actually be used with other visuals to
- 03:10 make a more complex filter context.
- 03:13 We also have this little chart over here which is called Amount by Account Group.
- 03:17 And you'll notice that revenues is selected.
- 03:19 So the big question then is what makes up the filter context
- 03:24 in this particular case?
- 03:26 The first thing is that we can apply the filter context from the year.
- 03:30 It's been selected as 2009.
- 03:32 Now in most visuals when we select something,
- 03:34 it clears the filter context from all of the other visuals.
- 03:37 In the case of a slicer, it sticks around and layers on, so
- 03:39 that's something we gotta be a little bit aware of here.
- 03:42 The second thing that contributes, 10 to 15 from the temperature range.
- 03:47 This is a specific value from a specific column, called temperature range,
- 03:50 that adds to the filter context.
- 03:54 We also have July from the month short call
- 03:57 that also adds to the filter context as well as the revenues value that
- 04:02 has been selected in the amount by account group column.
- 04:05 So the filter context is made up of year = 2009, and to the temperature range is ten
- 04:10 to fifteen, and month short equals July, and the account group equals revenue.
- 04:15 That is our complete and consolidated filter context.
- 04:19 What we then do is we actually apply the filter context to the underlying source
- 04:23 tables.
- 04:24 This filters down and reduces the number of rows that are going to be used
- 04:28 when calculating the particular measures.
- 04:31 So we get rid of all of the records that are not relevant,
- 04:34 all the stuff that we didn't select here.
- 04:37 And then we perform the measures arithmetic on whatever is left over and
- 04:40 return that to the visual.
- 04:41 So in this case, we get rid of all the rows that don't equal 2009.
- 04:45 We get rid of all of the rows where temperature range doesn't equal 10 to 15.
- 04:48 We get rid of all the rows where month short doesn't equal July, and
- 04:51 account group doesn't equal revenue.
- 04:53 Whatever rows are left, we then take the amount column, apply the average to it,
- 04:58 and return that to the cell.
- 04:59 And then the next thing is for the 226.72,
- 05:03 we start all over again from raw unfiltered tables.
- 05:07 It starts the entire process over.
- 05:09 So that happens for the 226.72, it happens for the 317, it happens for the 185.
- 05:14 And most importantly, when it gets to the total it does the same thing.
- 05:18 The only difference on the total row is that there's no filter context for
- 05:22 temperature range because all of the values are used in that particular
- 05:26 one, okay?
- 05:26 So that's the big difference here.
- 05:29 When you get into debugging measures and things aren't working, follow this chart.
- 05:33 This is really useful to understand.
- 05:35 If you can work out what the filter context is and how the process goes,
- 05:39 you will be able to debug any measure that uses a basic measure pattern.
Lesson notes are only available for subscribers.