Home » Power BI » Row Context and Filter Context in DAX

Row Context and Filter Context in DAX

ExcelMojo Team
Article byExcelMojo Team
Avatar
Reviewed byDheeraj Vaidya, CFA, FRM

What is Row Context in DAX?

In DAX (Data Analysis Expressions), row context refers to the context in which a single formula or expression is being evaluated for each row of a table. It’s the context provided by the current row being processed. In other words, when DAX is calculating a formula for a specific row, it considers the values in that row to perform calculations.

Context is a critical concept that must be understood to build and troubleshoot any formula for performing dynamic analysis or calculations. When you define a context, it changes the results of a formula to reflect the current row or any cell selected or other related dataset. Hence, row context for any calculated column comprises the values stored in each single row and the values in columns that are related to the current row. It is possible to get a value from the current row and leverage those values for performing data operations over another table using a few existing functions, such as EARLIER or EARLIEST.

  • Row context can follow any relationships defined between various tables.
  • It uses these relationships to assess the rows in the related tables linked to the current row.
  • You can also use multiple-row contexts, which can have multiple current rows and current-row contexts.

What is Filter Context in DAX?

Filter context is the context provided by the filters applied to the data in a column or table using an argument to a formula. It includes filters from slicers, report filters, and relationships between tables. Filter context influences the calculations by determining which data is visible or considered for the computation.

The filter context is crucial for understanding how DAX calculations interact with the data model. When you create a DAX formula, it operates within the context of the data it’s evaluating. The filter context defines which rows and columns from the underlying tables are considered for the calculation. Filter context applies on top of any other contexts (row context or query context) or filters. Understanding filter context is crucial when working with DAX formulas to ensure that calculations provide the expected results based on the context in which they are being used.

Table of contents
Key Takeaways
  • Understanding row and filter context is crucial for writing effective DAX formulas. Row context is inherent in calculated columns, while filter context is prominent in measures.
  • Context transition functions like CALCULATE can be used to switch between row and filter contexts.
  • Be mindful of how contexts interact, as it can impact the results of your DAX calculations in Power BI or other tools using DAX.

How to Use Row Context in DAX?

There are multiple ways you can use row context in DAX. Some of these methods are highlighted below:

  • Row context in a calculated column
    • To use a row context in a calculated column, define the formula using DAX. For example, The below DAX formula will calculate the Previous Day’s Inventory
      • PreviousDayInventory = CALCULATE(SUM(Table[Inventory]), DATEADD(Table[Date], -1, DAY))
    • If the table where you are adding a calculated column is related to another table then you can also use the RELATED function to define your row context in DAX
      • For example: IncomeTaxCalculated = [Gross Salary]*RELATED(‘Tax’[TaxRate])
  • Row context in a measure
    • To use row context in a measure, you can define a DAX formula using CALCULATE and FILTER as highlighted below
      • YTD Sales = CALCULATE(SUM(Table[Sales]), FILTER(ALL(Table), Table[Date] <= EARLIER(Table[Date])))
  • Multiple row context
    • To use multiple row context, you can use EARLIER function using DAX shown below
      • =MAXX(FILTER(Sales,[ProdKey]=EARLIER([ProdKey])),Sales[OrderQty])

How to Use Filter Context in DAX?

Similar to row context, there are multiple ways you can use filter context in DAX. We have highlighted some of the methods below:

  • Use functions such as SUMX, COUNTX, AVERAGEX, etc. to use filter context.
  • Use Filter functions (CALCULATE, CALCULATETABLE, EARLIER, EARLIEST, FILTER, INDEX, ROWNUMBER, SELECTEDVALUE, etc.) to create a filter within the DAX formula.
  • Use ALL function to clear any filters for performing a grand total calculation.
  • Use ALLEXCEPT function to clear any selections and apply filters within the formula.

Example of Row Context in DAX

In this section, we will demonstrate how to use row context in DAX in Power BI Desktop. For this demonstration, we will use the US_cities_2022 dataset which includes basic data about all US cities with a population over 100.000 (333 cities). This file has 2 datasets.

  • US_cities_2022: US cities dataset covering geographical coordinates and population details.
  • City-state Mapping: US cities to state mapping dataset.

For row context, we have used the RELATED function to create a calculated column that is related to another table using the DAX formula to perform calculations.

To use the row context in DAX, follow the steps highlighted below:

  • Step 1: Open Power BI Desktop, navigate to the Home tab, and select Get data option
Row context in DAX - Example 1 - Step 1
  • Step 2: Choose your data source to be loaded into Power BI Desktop
  • Step 3: Select the data source in the Navigator window and click on the Load button.

Here we have selected both City-State Mapping and US_cities_2022 datasets on the Navigator window.

Example 1 - Step 3a

Once the data is loaded into the Power BI, you can view it on the Fields pane.

Example 1 - Step 3b
  • Step 4: Navigate to the Fields pane, right-click on the US_cities_2022 table, and select the New column option from the menu table.
Example 1 - Step 4
  • Step 5: Enter the DAX expressions in the formula bar to create the Power BI columns for the US_cities_2022 table.

We have created a State column for the US_cities_2022. This column State captures the State details for each of the US cities available in the US_cities_2022 dataset.

State = RELATED('City-State Mapping'[State])

Example 1 - Step 5
  • Step 6: Click on the Commit icon to save the changes for the calculated column.
Example 1 - Step 6
  • Step 7: Navigate to the Visualization pane, choose the Card visual, and drag and drop the attributes from the Fields pane to the report canvas to create a card visual.
  • Step 8: Navigate to the Visualization pane, choose the Slicer visual, and drag and drop the attributes from the Fields pane to the report canvas to add the slicers to your Power BI visual.
Row context in DAX - Example 1 - Step 8
  • Step 9: Navigate to the Format your visual tab under the Visualization pane to apply the formatting options to make it visually appealing.

Example of Filter Context in DAX

In this section, we will demonstrate how to use filter context in DAX in Power BI Desktop. For this demonstration, we will use the material footprint per capita by country dataset, which is a comprehensive resource assessment metric designed to examine disparities and inequalities in resource utilization within societies. For filter context, we have used the FILTER function to apply filter restrictions to the underlying dataset using the DAX formula to perform calculations.

To use the filter context in DAX, follow the steps highlighted below:

  • Step 1: Open Power BI Desktop, navigate to the Home tab, and select Get data option.
Filter context in DAX - Example 2 - Step 1
  • Step 2: Choose your data source to be loaded into Power BI Desktop.
  • Step 3: Select the data source in the Navigator window and click on the Load button.
Example 2 - Step 3a

Once the data is loaded into the Power BI, you can view it on the Fields pane.

Example 2 - Step 3b
  • Step 4: Navigate to the Fields pane, right-click on the material_footprint table, and select the New column option from the menu table.
Example 2 - Step 4
  • Step 5: Enter the DAX expressions in the formula bar to create the Power BI columns for the material_footprint table.

We have created the Total Material footprint per capita (2021) for AfroAsian Continent column for the material_footprint. This column provides the total material footprint per capita for both Asia and African continents for the 2021 calendar year.

Total Material footprint per capita (2021) for AfroAsian Continent = SUMX(FILTER(material_footprint,material_footprint[Continent] in {"Asia","Africa"}),material_footprint[Material footprint per capita (tonnes) (2021)])
Example 2 - Step 5
  • Step 6: Click on the Commit icon to save the changes for the calculated column.
  • Step 7: Navigate to the Visualization pane, choose the Card visual, and drag and drop the attributes from the Fields pane to the report canvas to create a card visual.
Filter context in DAX - Example 2 - Step 7
  • Step 8: Navigate to the Format your visual tab under the Visualization pane to apply the formatting options to make it visually appealing.

Important Things to Note

  • Row context is automatically applied when you create calculated columns, whereas measures are evaluated in filter context.
  • When you are using row context, you can’t reference either a value from the row before the current row or any arbitrage single cellular value.
  • Calculations in row context are done row by row, while calculations in filter context are done considering the filters applied.
  • It is recommended that before using any filter context for result interpretation, you should review the definition of measures, or any formulas used in a Pivot table.

Frequently Asked Questions (FAQs)

1. What happens when a formula is used in a calculated column, and how does it relate to row context?

When you use a formula in a calculated column, the formula is evaluated for each row in the table, and the result is stored in the new column for that particular row.

The row context of that formula used in a calculated column encompasses all the values from all the columns applicable to the current row. If the table you are referring to is related to a different table, then the row context includes all the values from the other table that are relevant to the current row.

2. How does the use of iterators impact row and filter context in DAX?

DAX provides iterators like SUMX, COUNTX, etc., which iterate through a table or a table expression and can accommodate multiple current rows and current row contexts.

Row context is the context of the current row being processed, while filter context is the set of filters applied to a calculation.

Iterators can modify both row and filter context. They iterate through the rows of a table, applying the expression for each row, and they can also modify the filter context by evaluating expressions in a context modified by outer filters.

3. How does the context affect relationships in DAX?

In DAX, relationships between tables create a context for calculations. There are two types of context: row context and filter context.
Row context is established when you refer to a column in a formula, and it is defined for each row in a table. Filter context is created by filters applied to a calculation. For example, if you filter data by a certain category, the filter context is set for that category.

Context includes all the related tables and filters on the datasets where there are multiple tables linked by relationships and filtered datasets (through slicers). Based on the context applied, the output results may change dynamically.

4. What is the difference between row context and filter context in terms of time intelligence functions?

Time intelligence functions in DAX, such as TOTALYTD, TOTALQTD, and TOTALMTD, are designed to work with dates and handle time-related calculations. Row context and filter context play a crucial role in time intelligence functions. The filter context can further influence the calculation based on applied filters.

Guide to Row Context and Filter Context in DAX. We learn multiple ways to use the ROW and FILTER context in Power BI with examples. You can learn more from the following articles –

Power BI Query

Power BI Date Formatting

Power BI WorkSpace

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *