DAX Mastery: Row and Filter Contexts (Day 2)

Imagine you are a teacher with a stack of report cards, one for each student. Your goal is to review each student’s performance across all subjects. Here’s how you approach it:

• Step-by-Step Examination: You pick up the first report card and look at it closely. You see that the student has grades in math, science, English and other subjects.
• Repeating the Process: You then pick up the next report card and do the same, continuing this process for every report card in the stack

In this scenario, Row Context is like examining each individual report card in detail, one by one, to see all the grades for each student. You are focusing on all the information within each single report card.

Now, imagine you want to find out which students scored above 85 in math. Instead of manually looking through each report card, you ask your assistant to filter out the report cards and give you only the ones where the student scored above 85 in math.

• Applying a Condition: Your assistant goes through the entire stack and selects only those report cards that meet your criteria.
• Focused Review: You now have a smaller stack of report cards, each showing students who scored above 85 in math.

In this scenario, Filter Context is like applying a condition to your stack of report cards to show only those that meet your criteria (students who scored above 85 in math). You are narrowing down the pile of report cards to focus only on the ones that are relevant to your specific interest.

In DAX engine, Row Context refers to the environment in which a particular row’s data is considered. Let’s break it down:

• Detailed Examination: Just like examining each report card for all subjects, Row Context involves looking at each row in a table and evaluating its data.
• Calculations: When performing calculations like adding a new column that calculates the total score for each student, you are operating within the Row Context. Each calculation considers all the data within that specific row.

Example in DAX:

Total Score = Students[Math] + Students[Science] + Students[English] 

In this example, the calculation of the Total Score for each student considers all the subjects (columns) within each individual student’s record (row).

Filter Context:

Filter Context is the environment that determines which rows of data are visible to a calculation or formula. Here’s how it works technically:

• Applying Filters: Just like asking your assistant to filter out report cards based on math scores, Filter Context applies conditions to your data to narrow down the rows.
• Narrowing Down: This context changes the view of the data so that calculations are performed only on the filtered rows.

Example in DAX:

High Math Scores = CALCULATE(SUM(Students[Total Score]), Students[Math] > 85)

In this example, the CALCULATE function modifies the context to include only those students who scored above 85 in math. The SUM function then calculates the total score only for these filtered students.

Combining Row and Filter Contexts:

Often, both contexts are used together to perform complex data analysis. Let’s combine our earlier examples:

1. Applying Filter Context: First, filter the report cards to find students who scored above 85 in math.
2. Using Row Context: Within this filtered set, calculate the total score for each student.

Example in DAX:

High Achievers' Total Score = 
CALCULATE(
    SUMX(
        FILTER(Students, Students[Math] > 85),
        Students[Total Score]
    )
)

• FILTER Function: Creates a Filter Context to include only rows where students scored above 85 in math.
• SUMX Function: Iterates over each row in this filtered set and calculates the total score for each student.

This example shows how Filter Context (finding students with high math scores) and Row Context (calculating total scores) can interact to perform detailed and specific data analysis.

An important aspect of Row and Filter Contexts is how they work together, especially in nested calculations. This interaction can create powerful and dynamic analyses that go beyond simple filtering or row-based calculations.

Consider an advanced scenario

Top Math Students with High Science Score = 
CALCULATE(
    AVERAGEX(
        FILTER(Students, Students[Math] > 85),
        Students[Total Score]
    ),
    Students[Science] > 75
)

• Step 1: First, find students who scored more than 85 in math.
• Step 2: From these students, calculate the average of their total scores.
• Step 3: Further refine this group to include only those who also scored more than 75 in science.

This layered filtering and calculation approach allows DAX to perform complex, multi-dimensional data analysis efficiently and insightfully.

By applying filters early in the calculation process, you reduce the number of rows that subsequent operations need to process. This can make your calculations more efficient, as you’re working with a smaller subset of data.

Practical Tips for Performance:

Use FILTER Sparingly: While FILTER is powerful, it can be slow if used excessively. Try to combine filters logically and apply them in a way that minimizes the number of rows processed.

Leverage CALCULATE Wisely: CALCULATE is essential for modifying filter contexts, but overusing it can lead to performance bottlenecks. Structure your DAX queries to balance the use of CALCULATE with other functions.

Click below link for next part:

DAX Mastery: Types of Functions (Day 3)

Don’t forget to subscribe to

👉 Power BI Publication

👉 Power BI Newsletter

and join our Power BI community

👉 Power BI Masterclass