3: Building a Data Model to support self-service reporting
3.9 Understand contexts
3: Building a Data Model to support self-service reporting
3.9 Understand contexts - Video Tutorials & Practice Problems
Video duration:
8m
Play a video:
Video transcript
<v ->Okay.</v> So in this sub lesson we're gonna define the row and filter context. And then go through and do demonstrations on both. First, let's define the row context. The row context is applied as a virtual pointer which moves down a table of data row by row and is used in a calculation. So what that means is on a row by row basis, when you go and apply a calculation it's gonna take the values off of that individual row for the columns you're looking at and make the calculation. We already saw that on the line total sales and we'll go through the demonstration on this momentarily again to remind you of the row context. The next context we're gonna define is the filter context. And the filter context is the context that is applied by taking all filters that surrounded DAX expression and then applying the filters to expression to affect its results. Some areas where contexts may apply to effected are, within a visual, at the visual level, the page level, what is referred to as the report level, or as you will see an interface is called the all pages, inside a DAX expression and within our visual interactions. So, the one thing that is really fun about context is that you can influence them all over the place. Now that is part of the power and part of the challenger context, is you can do a lot with them. But sometimes if you begin using them too much, they can become a little bit confusing. So it's really good to start with a base level of knowledge around what the contexts are and start working your way progressively through more complexity. So what I'm gonna do here now is let's get ourselves into the Power BI desktop file is gonna support the sub lesson and go ahead and demonstrate contexts. Okay, so let's get the Power BI desktop file open that supports the sub lesson. We'll go ahead and double click there. Give it a moment to go ahead and open. Okay, so our desktop file is open. So, let's go over to the data view here once again. And, we will go back over to our sales table. Like so. Click on that. And let's go over to our line total sales. So, right here this is the calculator column that we create a little bit earlier. So if I go ahead and highlight on it, you're gonna see the column gets highlighted over here in the data preview. And, I just wanna point something out here that I did not do in the last sub lesson. Is if I had to zoom in here, we're gonna see that the line total sales calculated column has a special symbol next to it. So this is the symbol that indicates that we have created a calculated column. So this is a calculated column. One who was Sigma has a default summarization. And down below here, the one with a calculator is the measure. So just to recap what some of these icons actually mean. And I'm gonna zoom myself back out. So, let's focus in on the row context now. So what did I do is just take the DAX expression here. Make it a little larger so we can see it. And, what is happening here, is if you think of my pointer as being the pointer which moves down the table row by row. Right now, the first row is being calculated as the order quantity times unit price. And it does the calculation. Comes up with the value 1497. The pointer will then move down to the next row. Do the exact same calculation. Come up the with value, moved on the next row. And this pointer moves down the table really quickly. Once again, like I said across the 60,308 rows almost instantaneously. And what is happening here, is this calculation is being applied to every row within this column using the row context. So the row context is a fairly straightforward context to get your head wrapped around. You can visually see what's going on. And it tends to make a lot of sense. The filter context does take a little bit more getting used to. So let's go ahead and take a look at the filter context. So we're gonna do now is move ourselves over to the report view. And for this demonstration here, I'm gonna go ahead and open up our filters pane. So, I wanna show a number of different places where we can influence the filter context. So what I'm gonna do here is I'm actually just going to make this visualization a little bit smaller. Just by resizing it here. And, I'm gonna take a couple of copies of this visual. So I'm just gonna make sure it's highlighted. Hold control C, control V like so. Create a second copy of that visual. Just move it over here, like so. So now on this report page, I have two the same visual. I'm also gonna go ahead and create a second page and paste the visualization here as well. So now I've got three copies of the exact same visual. Page number two has one visual. Page number one has two of the visuals here. Okay, so let's start at the very top here. So, we mentioned before that we can influence the filters on all pages which I mentioned earlier on is referred to as the report. So, what I'm gonna do here is go over to my order date field. And go ahead and find the order date years. So I'm just gonna make this a little bigger so I can actually see these columns a little bit easier. I'm gonna open the hierarchy up and choose order date years. So I'm gonna highlight on it here. And I'm gonna go ahead and drag it in this filters on all pages. We'll go ahead and drop it there. I'm gonna choose the dropdown box next to filter type and say basic filtering. And here I'm going to choose the year 2019. All right. So I go ahead and choose 2019. You're gonna notice that both the visuals on this page excepted that filter of 2019 as does the visual over in this page. And that's because we applied the filter at the all pages level. Which means all pages and all visuals on the pages will accept that filter unless we explicitly keep it from happening. For now we're just gonna allow that to cascade down and all of those to be accepted. So I'm just going to go ahead and get rid of the filters on all pages. And, I'm gonna do the exact same thing at the page level. So, this time I'm actually gonna go over to page number one. And I'm going to once again go to my order date year, drag the year onto this filters on this page. And, do the same thing, go to filter type basic, choose 2019. And we're gonna see that both the visuals on this page accepted that filter. If I go to page number two, this visualization did not accept that filter 2019. And that's because we applied the filters on this page. So only impact in this one here. So I can go ahead and turn this off. I also have the ability to put a filter on a visualization. So, I'm just gonna highlight this visual right here. And we can see by default the order date year, given that we have it on the visualization already shows on filters on this visual. And it's showing all the years. If I want I can click this dropdown here. Go to filter type. Choose basic filtering once again and choose 2019. And now we'll notice this visual accepted that filter but this one didn't. Because now we're actually applying the filter at the visualization level. So I'm just gonna go ahead and clear that, get rid of that filter there. And we've seen so far where we can affect the filter context in a number of different ways. So we did it at the all pages level, the page level, the individual visual level and then we can see here as the visuals drawing itself out this is another spot where the filter context occurs. So keeping in mind this one row right here, it is running the measure for total sales. And it is filtering it down to 2017. It's running the measure for total sales here again and running it for 2018. So, each one of these intersection points is another filter within the actual visualization itself. And we're gonna show you some additional ways as we move through the rest of these sub lessons on how to affect the filter context within a DAX expression. And then later on, when we get in the visualizations we're gonna talk about how we can do that through editing the interactions through the visualizations themselves. Okay, so that brings us to the end of the sub lesson here on context.