All language subtitles for 8. Filter Context Examples in Power BI

All right.

So we've talked quite a bit about filters in the context of our data model and how filter or context

can transmit through table relationships.

But now we're going to talk about filter context and how it impacts daks measures.

So remember the definition of a measure it's evaluated based on filter context which means that those

measures recalculate whenever the fields or filters around them change.

So consider this matrix view on the left side of the screen.

If we pinpoint one cell out of that matrix we can understand exactly what filter context is being passed

to that cell and therefore understand exactly how that measure is being calculated.

So for this particular highlighted cell we're calculating a measure called total orders and it's evaluating

based on the following filter context the product name column from the product table is equal to Turing

tire tube because that's the filter context passed to this cell from the Matrix row labels.

So this is pretty intuitive on the surface.

This allows to measure the total orders measure to return the order quantity for each product specifically

or whatever the matrix row and column labels dictate.

It could be years could be countries product categories customer names.

So again pretty straightforward but what might not be quite so straightforward is the fact that total

or grand total values are not simply calculated by summing the values above they're calculated as their

own unique measures with no filter context.

So in this case since we aren't calculating orders for a specific product in the Total row we're essentially

taking that filter context of product name and ignoring it.

So it's a nuanced concept but a very important idea which is that each measure in a report behaves like

its own island and calculates according to its own unique filter context even totals and Grand Totals

values.

So as I was learning about filter context for the first time and really digging in and trying to wrap

my head around what it really meant what was most helpful for me was working through a number of different

variations of examples.

So let's take a minute to do that right now here I've got a sample view of a dashboard.

This is a piece of a dashboard that we're going to be building in the next section of the course.

And obviously you've got a lot of information here.

Got a matrix on the left.

You've got donut charts combo charts tree maps cetera.

One thing to pay attention to is this report level filter in the lower right that says Year is 2016

or 2017.

We're going to talk all about what those filters mean and how they work.

But for now all you need to know is that this filter applies to every visual and every number that you

see on this entire report.

So from here we can actually pinpoint individual measure values to understand what filter context is

being passed to them.

So for example this first case in the top left or evaluating measure named total revenue the filter

context is that year equals 2016 or 2017 and that the full name from the customer table equals Mr. Larry

Munoz.

So with that filter context in mind the DAX calculation for the total revenue measure evaluates and

spits out the result that we see here in this case it looks like about ten thousand eight hundred fifty

two dollars of revenue.

So look at another example here.

Now we're not looking at an actual numerical value we're looking at a component of a chart which is

built from a value.

In this case this is a donut chart that's tracking total orders by gender.

So the measure driving this segment of the donut chart as filter context of year equals 2016 or 2017

because remember all visuals all charts all numbers in this report will be subject to that report level

filter.

And then the second piece of the filter context is that gender equals F for female.

Similar case here.

We're evaluating total orders again.

Got that same 2016 2017 filter context.

The only difference is that this chart is breaking down orders by occupation.

So in this particular highlighted cell or segment of the donut.

The second piece of filter context is that the occupation field in the Customer table equals clerical

moving down to the bottom left.

Here's an interesting one.

In this case we're looking at the Total row for a measure called total orders.

So even though this value lives in a matrix that's broken down by customer names because this measure

this little island cell needs to calculate the total we're basically calculating orders in the absence

of a filter with the exception of that one page level filter that applies across the board.

So that number twenty two thousand five hundred thirty four reflects the total orders as defined by

the measure formula for 2016 or 2017.

Moving along to this trended view where you've got columns capturing total orders and a line capturing

total revenue by month.

In this case this highlighted column here capturing total orders years 2016 or 2017 and month from the

calendar table is August 2016.

So this one's a little bit interesting because you've got two different filters from the calendar table

being applied and in cases like this it's the more specific filter that's going to take priority.

Now last example looking at this card visual in the report in this case this number is acting almost

like a grand total.

There are no labels or column labels or chart axes or visual level filters that will be impacting the

value shown here.

So what we're looking at is just the total revenue measure subject to those report level year filters.

So hopefully these examples are helpful.

I imagine that you're thinking to yourself you know pretty straightforward pretty intuitive.

But the thing is it does get a bit more complicated and a bit more interesting.

To be honest because these aren't static views these are interactive dynamic dashboards and visuals.

And what I mean by that is that users can drill into components of this report and components of these

visuals in order to drill in and explore this data in different ways.

So you could drill in and isolate a specific row in a matrix or Drilon to filter down to a specific

segment of a chart.

And when that happens more filter context is essentially being added to every single measure shown on

the report.

So what that means is that the context that we see here that we've listed out this is just a snapshot

at one particular point in time.

But if this donut chart for instance that breaks down order by gender was configured to interact with

all of the other visuals in this report page then that means that a user could potentially click on

the female segment and add a new context to every one of these other measures that we've called out

as well as every other measure in the entire page for customer gender equals female.

And if that user then drills deeper into a particular income level segment that adds another line of

filter context to every measure on the page.

So as you can see it can get quite a bit more sophisticated and quite a bit more complicated.

That's why it's really important for us to nail down these fundamentals and understand exactly what's

going on.

So we're going to talk through a number of examples when we start writing daks functions in the second

half of this section.

And then this will come into play in a number of different ways.

In the final section of the course when we actually start building visuals and reports so stay tuned.

Some great stuff ahead.