Row Context & Filter Context in DAX

In this article, I am going to explain what is Row Context & Filter Context in DAX.

First, we know what is context.

Context enables you to do dynamic analysis, in which the results of a formula can be changed to reflect the current row or cell selection and any associated data. An understanding of context is essential for building high-performance formulas, dynamic analysis, and troubleshooting problems in formulas.

There are different types of contexts:

1. Row Context
2. Filter Context

So, let’s discuss what is Row context and filter context in short

Row Context does calculations of each row with value within a row and it performs row-by-row operations.

If you created a calculated column, the row reference includes the values ​​for each row and the values ​​for the corresponding columns from the current row.

The filter context is the set of filters applied to the data model before the evaluation of a DAX expression starts. It is the set of filters that are applied before that table arrives for use.

Some initial level filters context applied on the Power BI report page in the form of:

• Slicer
• Filter through other Visual
• In Matrix visual based on Row and column data field
• Power BI filters

So, I will show you in a simple example that we easily understand the filter context and row context.

You see, filter and row context can be seen in common situations if you know what you are looking for. I ride a bus and you know there are two different types of seats on a bus.:

1. Window Seats
2. Aisle Seats

I have sketched out what I mean here.

Unleash the Potential of Power Platform With a Center of Excellence

Business innovation often comes from within. Discover how to empower innovation from non-traditional developers with the Microsoft Power Platform.

Learn More

Here you can see that in this bus there are two types of seats and a driver’s cabinet.

Now, what do filter and row context mean here?

Let’s pretend you are a fare inspector. You need to find a family on the bus and find out how much they had spent on the fares. You know their seat numbers and they are all sitting at window seats.

In this scenario, you will need to go inside the bus and go to the window seats and tally up their ticket prices. This, in its simplest form, demonstrates filter context and row context.

Here the filter context would be window seats. The row context would be the individual seats. The tally of how much they have spent would be the aggregation.

This slide shows the filter context.

If you translate this into Power BI, this would be a measure of something like this.

Here you would tell Power BI to go inside a bus, go down the window seats, and find the family group — this is the filter context.

What do you want to do with this? Power BI goes to the location but doesn’t know what to do. It needs to know what to do when it gets there! This is row context. Here with SUMX, you are telling Power BI, to sum up, row by row, seat by seat, the ticket price.

Now for another example, what if I want to know, the total price of the window seats and aisle seats by occupancy?

You, being the fare inspector, would once again go inside the bus, and walk to the window seats and aisle seats. Now, what do we do? Now we need row context. What are we doing by row? We need to add up the cost of the window seats and aisle seats. This is the SUMX in Power BI. You will add up the price by the window seat and add the price of the aisle seat and move on to the next row and do the same….

Iterators in DAX follow the below-mentioned steps:

• Evaluates the first parameter in the existing context (read filter contexts) to determine the rows to scan.
• Creates a new row context for each row of the table evaluated in the previous step.
• Iterates the table and evaluates the second parameter in the existing evaluation context, including the newly created row context.
• And last, aggregates the values computed during the previous step.

Important Points:-

Filter Context:

• Can be modified by the functions like CALCULATE ( ).
• Bidirectional filtering can be forced.
• Comes initially from the visual coordinates.
• Follows the one-to-many relationships automatically.

Row Context:

• Exists in special DAX functions like SUMX and FILTER.
• Does not follow relationship.
• A row Context does not create a filter context.
• Exists in calculated columns.