Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# Explaining Row Context In Power BI

by | 9:00 am EST | December 13, 2019 | Business Intelligence, DAX, Power BI

I have found out that most people get confused in regards to understanding how DAX works in row context. This is because row context can be quite complicated and unnecessarily so. So we’re going to touch on row context throughout this article. You may watch the full video of this tutorial at the bottom of this blog.

The way I think about row context is through iterations or iterating functions. As soon as I made that distinction in my mind, it made more sense in terms of what was actually going on in the calculation engine.

As a recap, we have discussed that there are three types of contexts: the evaluation context, the filter context, and the row context.

Any DAX calculation works in a two-step process. The evaluation context always goes first and then it branches out into either filter context or row context depending on what function you write.

## Understanding Row Context

Row context is all about iterating.

I’m going to show you a couple of examples and hopefully this will help in your understanding of what row context actually does in the back end.

### Row Context VS Filter Context

In our example, the formula is being calculated via filter context because it is a simple aggregation. It sums up the entire Quantity column after all the initial filters have been put in place.

The initial filter on the Sales table is Product 1. By summing up every quantity that has been sold, we get 165.

Now I’m going to show you how to get the exact same result, but calculated in a very different way. First, I’m going to create a new measure for Quantity Sold Iteration using an iterating formula.

Examples of iterating functions include SUMX, AVERAGEX, MANYX, and COUNTX. In this case, we are going to use SUMX.

The SUMX function has asked me to put in a table, so I’m going to put in the Sales table. And then it asked me for an expression so I just put in the Quantity column.

This function is quite different to the first function, but I get exactly the same result when I drag it in.

We’re getting exactly the same results here because it is ultimately the same calculation. It was just calculated in a different way.

The Quantity Sold Iteration was calculated using row context, while the Total Quantity Sold was calculated using filter context.

### Calculating Quantity Sold Iteration Using Row Context

Let’s now go through step by step how the Quantity Sold Iteration is calculated. First, you have to remember to leave the initial valuation context, which is Product 1 in this case. And then via row context, we’re iterating through every single row in the table that we specify.

In this case, we’re specifying the Sales table and then we go and look at every single row in the Quantity column.

During the initial evaluation, Product 1 would be determined by whatever is filtered in the Product ID column. And then it counts up the rows under the Quantity column. Every time it hits a row it goes in and saves the result into memory.

After it reaches the bottom of the table, it then goes and does an evaluation of all the results that are saved into memory, and then it will do whatever calculation you ask of it, which in this case is SUMX.

### Why Use A Row Context Or An Iterating Function?

This is because inside an iterating function you can do far more complex things. You can write some very advanced logic inside.

For example, for every row in the Sales table, the quantity is multiplied by 2. You’ll see now that the same calculation process is occurring, but we’re getting different results.

This is because for every single row, we’re writing some different logic.  After going through every single row in the quantity column, we multiply the value by two, and then it gets saved into memory. At the end of that, we do a SUM.

We can also write different things here including advanced logic such as the IF or SWITCH logic.

There are heaps and heaps of examples of where it would be better to use an iterating function or row context to do a calculation rather than an aggregation function or filter context.

## Key Notes In Understanding Contexts In Power BI

To recap all the key things about context, remember that calculating in DAX works via a two-step process. The first step is always the evaluation context. It matters so much what the evaluation context is.

Once you understand what the evaluation context is for any individual result, DAX then branches out into two different ways. It can either calculate via filter context or via row context.

Choosing which context to use depends on what formula you are writing. So if you write a simple aggregation formula, it will then go and calculate it via filter context. But if you write an iterating function like SUMX, then it will calculate the formula via row context.

Once you’re getting slightly more advanced in DAX, you can actually have multiple contexts in the same function. But before you get there, you really have to understand what context is in its simplest form.

## Conclusion

Understanding the types of context is absolutely crucial as you learn DAX and want to do more advanced work. When you start writing really advanced DAX formula, you have multiple contexts working at the same time and you need to understand what each individual part of that formula is doing to achieve your desired results.

Evaluation Context – (1.8) Ultimate Beginners Guide to DAX

Filter Context – (1.9) Ultimate Beginners Guide to DAX

Enjoy working through this one.

Sam

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## Funny ChatGPT Prompts: 20 Hilarious ChatGPT Ideas

In a world where technology continues to amaze us, we have now arrived at the point where we can have a...

## Power BI Slicer Search: User Guide With Examples

Ready to get started with the Power BI slicer? This feature will allow you to filter and slice your...

## SUMPRODUCT Multiple Criteria: Explained With Examples

Most Excel users think that the SUMPRODUCT function in Excel helps only to multiply the numbers in...

## Akkio: Everything You Need to Know About It

In an era dominated by data, efficiently managing and interpreting vast amounts of information is...

## Julius AI: A Complete Guide + Use Cases Explained

With the advent of ChatGPT, automation and efficiency became a main goal for individuals and businesses...

## Data Analytics Outsourcing: Pros and Cons Explained

In today's data-driven world, businesses are constantly swimming in a sea of information, seeking the...

## How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

## The Top 5 Power BI Alternatives in 2023

Power BI has established itself as a powerful business analytics platform, offering a wide range of...

## How to Apply the Same Formula to Multiple Cells in Excel

One of Microsoft Excel's powerful features is using formulas to do calculations and changes to data in...

## Power BI Waterfall Chart: A Detailed User Guide

In the world of data visualization, charts speak louder than numbers. If you're looking for a way to...

## Power BI Import vs Direct Query: Which is Better & Why?

In the world of data analysis, Power BI offers you a range of tools to connect to your data sources....