Learning DAX (Data Analysis Expressions) is essential for building dynamic and powerful reports in Power BI. Before diving into complex formulas, it’s important to understand two basic building blocks: Calculated Columns and Measures. Once these are clear, we can easily understand Row Context and Filter Context.
In this article, you’ll learn:
- What Calculated Columns and Measures are — in simple words
- What Row Context and Filter Context mean in DAX
- How to use both with practical table examples
- Why understanding them makes Power BI so powerful
1. What is a Calculated Column
A Calculated Column is like adding a new column in Excel. You write a formula, and it calculates values for each row. The result is stored in your table and does not change when you use slicers or filters.
Example Table: Sales
| Product | Quantity | Price |
|---|---|---|
| Apple | 10 | 20 |
| Orange | 5 | 15 |
If we want to calculate the total amount for each product, we can add a calculated column:
Total Amount = Sales[Quantity] * Sales[Price]| Product | Quantity | Price | Total Amount |
|---|---|---|---|
| Apple | 10 | 20 | 200 |
| Orange | 5 | 15 | 75 |
Key point: Calculated columns work row by row and are stored permanently in the model.
2. What is a Measure
A Measure is like using a calculator. It doesn’t store values in the table. Instead, it gives you a result only when you use it in visuals (for example, a table, card, or bar chart). It responds to filters and slicers dynamically.
Example: To calculate total sales for all products, we can create a measure:
Total Sales = SUM(Sales[Total Amount])- If no filter is applied → result = 275
- If slicer selects only Apple → result = 200
Key point: Measures are calculated on the fly and react to filters in your report.
3. Difference Between Calculated Columns and Measures
| Calculated Column | Measure |
|---|---|
| Works on each row | Works on the whole table |
| Stored in the model | Not stored, calculated when needed |
| Does not change with filters | Changes with slicers/filters |
Total Amount = Sales[Quantity] * Sales[Price] |
Total Sales = SUM(Sales[Total Amount]) |
4. What is Row Context?
Now that you know what Calculated Columns are, it’s easier to understand Row Context. Row Context simply means DAX is calculating values row by row. Calculated Columns use Row Context by default.
For example, when you create:
Total Amount = Sales[Quantity] * Sales[Price]DAX goes to each row, multiplies Quantity and Price, and saves the result. It doesn’t care about filters or slicers here.
5. What is Filter Context?
Filter Context happens when a visual, slicer, or filter is applied. It tells DAX “look only at this part of the data.” Measures depend on Filter Context.
For example:
Total Sales = SUM(Sales[Total Amount])- If no slicer is applied → SUM of all products.
- If slicer = Apple → only Apple’s amount is calculated.
6. Example: Using a Calculated Column
Imagine applying a 10% discount on every product:
Discounted Price = Sales[Total Amount] * 0.9This creates a new column. The value for each row remains the same, no matter what slicers you use.
7. Example: Using a Measure
Let’s calculate average sales with a measure:
Average Sales = AVERAGE(Sales[Total Amount])This measure will change its result depending on the filters applied to the visual.
8. When to Use Calculated Columns
- When you need fixed values stored in the table.
- When the calculation is required at the row level.
- When the column will be used in relationships or filters.
9. When to Use Measures
- When you need totals, averages, or ratios.
- When you want values to change dynamically with filters.
- When you want to keep the model lightweight.
10. Practice Task
- Create the Sales table as shown above.
- Add a Calculated Column named
Total Amount. - Create a Measure named
Total Sales. - Create another Measure named
Average Sales. - Apply slicers on Product and observe how Measures react while Calculated Columns remain fixed.
11. Final Thoughts
Start by understanding Calculated Columns and Measures in their simplest form. Once this is clear, concepts like Row Context and Filter Context become much easier to grasp.
Calculated Column = fixed row-level value.
Measure = dynamic result based on filters.
As you advance, explore functions like CALCULATE() and FILTER() to build powerful DAX logic.
Keywords: DAX basics, Power BI, calculated columns, measures, row context, filter context, Power BI tutorial, beginner guide, DAX formulas, Power BI examples
0 Comments