In this post we are going to cover DAX fundamentals: measures, calculated columns, row context, filter context and CALCULATE. These are 7 real Power BI interview questions, and the last one is a scenario a manager loves to ask: the total row in a table does not match the rows above it.
DAX is the formula language of Power BI. You can write a lot of DAX without understanding context, right up to the day a number comes out wrong and you can't explain why. That is exactly what interviewers test. If you can explain filter context with a small example, you sound like someone who has fixed real reports.
Every example below runs on a small sample data set, so you can rebuild it in Power BI Desktop and check each number yourself.
Part 5 of the Real Time Power BI Interview Questions and Answers series on TechBrothersIT.
- The short answer
- Let's create some sample data
- Question 1: What is the difference between a measure and a calculated column?
- Question 2: What is the difference between row context and filter context?
- Question 3: What does CALCULATE do, and what is context transition?
- Question 4: What is the difference between SUM and SUMX?
- Question 5: How do you calculate percent of total, and what do ALL and REMOVEFILTERS do?
- Question 6: Why should you use variables and DIVIDE in your measures?
- Question 7: Scenario - the total row does not equal the sum of the rows. Why, and how do you fix it?
- The full SQL script
The short answer
A calculated column is computed per row at refresh and stored; a measure is computed at query time in the filter context of each visual cell. CALCULATE changes that filter context, iterators like SUMX work row by row, and totals are re-evaluated, not summed.
Keep that summary in mind. Every question below is one piece of it.
Let's create some sample data
We use a tiny shop: 5 products, 4 customers in two regions, a date table for 2025 and 2026, and 12 sales in January to March of each year. The script below is written for PostgreSQL (the date table uses generate_series), and it works on any database with small changes. Load the four tables into Power BI Desktop and relate fact_sales to each dimension on its key.
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_product (product_key INT PRIMARY KEY, product VARCHAR(30), category VARCHAR(20));
INSERT INTO dim_product VALUES
(1, 'Laptop Stand', 'Accessories'), (2, 'USB-C Cable', 'Accessories'),
(3, 'Monitor', 'Electronics'), (4, 'Keyboard', 'Electronics'),
(5, 'Desk Lamp', 'Home');
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, customer VARCHAR(30), city VARCHAR(20), region VARCHAR(10), email VARCHAR(40));
INSERT INTO dim_customer VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@tbshop.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@tbshop.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@tbshop.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@tbshop.com');
-- one row per day, 2025-01-01 .. 2026-12-31 (calendar year = fiscal year here)
CREATE TABLE dim_date (date DATE PRIMARY KEY, year INT, quarter VARCHAR(2), month_no INT, month VARCHAR(3));
INSERT INTO dim_date
SELECT d, EXTRACT(YEAR FROM d), 'Q' || EXTRACT(QUARTER FROM d), EXTRACT(MONTH FROM d), TO_CHAR(d, 'Mon')
FROM generate_series(DATE '2025-01-01', DATE '2026-12-31', INTERVAL '1 day') AS g(d);
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
order_date DATE REFERENCES dim_date(date),
product_key INT REFERENCES dim_product(product_key),
customer_key INT REFERENCES dim_customer(customer_key),
qty INT,
unit_price DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
( 1, '2025-01-10', 1, 1, 2, 40.00), ( 2, '2025-01-22', 3, 2, 1, 250.00),
( 3, '2025-02-05', 2, 3, 5, 10.00), ( 4, '2025-02-18', 5, 4, 1, 60.00),
( 5, '2025-03-03', 4, 1, 2, 50.00), ( 6, '2025-03-27', 3, 3, 1, 250.00),
( 7, '2026-01-08', 1, 2, 3, 40.00), ( 8, '2026-01-25', 3, 1, 1, 250.00),
( 9, '2026-02-11', 2, 4, 4, 10.00), (10, '2026-02-20', 4, 3, 2, 50.00),
(11, '2026-03-09', 5, 2, 2, 60.00), (12, '2026-03-30', 3, 4, 2, 250.00);Here are the 12 sales. Each row is one sale line: a date, a product, a customer, a quantity and a unit price.
| sale_id | order_date | product_key | customer_key | qty | unit_price |
|---|---|---|---|---|---|
| 1 | 2025-01-10 | 1 | 1 | 2 | 40.00 |
| 2 | 2025-01-22 | 3 | 2 | 1 | 250.00 |
| 3 | 2025-02-05 | 2 | 3 | 5 | 10.00 |
| 4 | 2025-02-18 | 5 | 4 | 1 | 60.00 |
| 5 | 2025-03-03 | 4 | 1 | 2 | 50.00 |
| 6 | 2025-03-27 | 3 | 3 | 1 | 250.00 |
| 7 | 2026-01-08 | 1 | 2 | 3 | 40.00 |
| 8 | 2026-01-25 | 3 | 1 | 1 | 250.00 |
| 9 | 2026-02-11 | 2 | 4 | 4 | 10.00 |
| 10 | 2026-02-20 | 4 | 3 | 2 | 50.00 |
| 11 | 2026-03-09 | 5 | 2 | 2 | 60.00 |
| 12 | 2026-03-30 | 3 | 4 | 2 | 250.00 |
And the products, so you can see which ones are Electronics, Accessories and Home.
| product_key | product | category |
|---|---|---|
| 1 | Laptop Stand | Accessories |
| 2 | USB-C Cable | Accessories |
| 3 | Monitor | Electronics |
| 4 | Keyboard | Electronics |
| 5 | Desk Lamp | Home |
Question 1: What is the difference between a measure and a calculated column?
A calculated column is computed once for every row when the model refreshes, and the result is stored in the table. A measure is not stored at all: it is calculated at query time, for each cell of a visual, using the filters that cell has.
Let's write one of each. The column multiplies quantity by unit price on every row. The measure adds up that column.
// calculated column on Sales
Amount = Sales[Qty] * Sales[Unit Price]
// measure
Total Sales =
SUM ( Sales[Amount] )Here is what the Amount column looks like for the first six sales. Each value is worked out on its own row: 2 times 40.00 is 80.00, 1 times 250.00 is 250.00, and so on.
| sale_id | qty | unit_price | amount |
|---|---|---|---|
| 1 | 2 | 40.00 | 80.00 |
| 2 | 1 | 250.00 | 250.00 |
| 3 | 5 | 10.00 | 50.00 |
| 4 | 1 | 60.00 | 60.00 |
| 5 | 2 | 50.00 | 100.00 |
| 6 | 1 | 250.00 | 250.00 |
Check this result yourself with SQL
A calculated column is computed for every row at refresh and stored in the model.
SELECT f.sale_id, f.qty, f.unit_price, f.qty * f.unit_price AS amount
FROM fact_sales f
WHERE f.sale_id <= 6
ORDER BY f.sale_id;So when do you use each? Use a column when you need to slice, filter or group by the value, like a price band. Use a measure for anything you aggregate, like total sales or margin percent.
| Column | Measure | |
|---|---|---|
| COMPUTED | at refresh | at query time |
| STORED | yes, uses memory | no |
| USE FOR | slice, filter, group | aggregate |
Every calculated column takes memory and makes the model bigger. When in doubt, make it a measure.
Question 2: What is the difference between row context and filter context?
Row context means "the current row". A calculated column, or an iterator like SUMX, looks at one row at a time, so it can read that row's quantity and price.
Filter context means "which rows are visible". In a visual that shows sales by region, the East cell only sees sales from East customers, and the West cell only sees West sales.
Let's write the Total Sales measure with SUMX and put it in a table by region.
Total Sales =
SUMX ( Sales, Sales[Qty] * Sales[Unit Price] )| region | total_sales |
|---|---|
| East | 1030.00 |
| West | 890.00 |
Check this result yourself with SQL
The same measure gives a different answer in each cell, because each cell filters the model differently.
SELECT c.region, SUM(f.qty * f.unit_price) AS total_sales
FROM fact_sales f JOIN dim_customer c ON c.customer_key = f.customer_key
GROUP BY c.region
ORDER BY c.region;The same measure returns 1030.00 for East and 890.00 for West. Nothing in the formula mentions a region: the filter context of each cell does that work. A total cell with no region filter sees all 12 rows.
The sentence to say in the interview: row context does not filter. It only points at a row. Only filter context decides which rows a measure aggregates.
Question 3: What does CALCULATE do, and what is context transition?
CALCULATE evaluates an expression in a modified filter context. Let's take Total Sales and add a filter on the product category.
Electronics Sales =
CALCULATE (
[Total Sales],
'Product'[Category] = "Electronics"
)| region | total_sales | electronics_sales |
|---|---|---|
| East | 1030.00 | 850.00 |
| West | 890.00 | 600.00 |
Check this result yourself with SQL
CALCULATE adds the filter Category = Electronics on top of the region filter from the visual.
SELECT c.region,
SUM(f.qty * f.unit_price) AS total_sales,
SUM(CASE WHEN p.category = 'Electronics' THEN f.qty * f.unit_price END) AS electronics_sales
FROM fact_sales f
JOIN dim_customer c ON c.customer_key = f.customer_key
JOIN dim_product p ON p.product_key = f.product_key
GROUP BY c.region
ORDER BY c.region;The region filter from the visual is still there, and CALCULATE adds the category filter on top of it. East sold 1030.00 in total, of which 850.00 was Electronics. West sold 890.00, of which 600.00 was Electronics.
CALCULATE also does context transition. When it runs inside a row context, like an iterator or a calculated column, it turns the current row into a filter. That is also why a measure used inside SUMX respects the current row: every measure reference is wrapped in an implicit CALCULATE.
Remember how filter arguments behave:
- A filter on a column that is not filtered yet is added.
- A filter on a column that is already filtered replaces that filter (unless you wrap it in KEEPFILTERS).
- ALL and REMOVEFILTERS remove filters.
Question 4: What is the difference between SUM and SUMX?
SUM adds up one column. SUMX is an iterator: it goes through a table row by row, evaluates an expression for each row, and then adds the results.
Here is the classic mistake, next to the right way.
Wrong Sales =
SUM ( Sales[Qty] ) * AVERAGE ( Sales[Unit Price] )
Total Sales =
SUMX ( Sales, Sales[Qty] * Sales[Unit Price] )| wrong_sum_x_avg | right_sumx |
|---|---|
| 2860.00 | 1920.00 |
Check this result yourself with SQL
Multiplying two aggregates is not the same as aggregating a row-level product.
SELECT CAST(SUM(qty) * AVG(unit_price) AS DECIMAL(10,2)) AS wrong_sum_x_avg,
SUM(qty * unit_price) AS right_sumx
FROM fact_sales;Sum of quantity times average price gives 2860.00, but the real revenue is 1920.00. Multiplying two totals is not the same as totaling a product. SUMX multiplies quantity by price on each row first and then adds, so it gets the right answer without needing a stored Amount column.
AVERAGEX, COUNTX, MINX, MAXX and RANKX work the same way: evaluate per row, then aggregate.
Question 5: How do you calculate percent of total, and what do ALL and REMOVEFILTERS do?
Divide the sales in the current context by the sales with the category filter removed. REMOVEFILTERS on the category column does exactly that, and ALL is the older way to write the same thing.
% of Total =
DIVIDE ( [Total Sales],
CALCULATE ( [Total Sales],
REMOVEFILTERS ( 'Product'[Category] ) )
)| category | total_sales | pct_of_total |
|---|---|---|
| Electronics | 1450.00 | 75.52 |
| Accessories | 290.00 | 15.10 |
| Home | 180.00 | 9.38 |
Check this result yourself with SQL
The denominator removes the category filter, so every row divides by the grand total of 1920.
SELECT p.category, SUM(f.qty * f.unit_price) AS total_sales,
CAST(100.0 * SUM(f.qty * f.unit_price) / (SELECT SUM(qty * unit_price) FROM fact_sales) AS DECIMAL(5,2)) AS pct_of_total
FROM fact_sales f JOIN dim_product p ON p.product_key = f.product_key
GROUP BY p.category
ORDER BY total_sales DESC;Electronics is 1450.00 out of 1920.00, which is 75.52 percent. Accessories is 15.10 percent and Home is 9.38 percent. Every row divides by the same grand total of 1920.00 because the denominator ignores the category filter.
A good follow-up answer: if the report has slicers and the user wants percent of what they selected, use ALLSELECTED instead.
| Function | Denominator is |
|---|---|
| REMOVEFILTERS / ALL | the grand total, ignoring the category filter |
| ALLSELECTED | the total of what the user selected in slicers |
Question 6: Why should you use variables and DIVIDE in your measures?
Variables (VAR and RETURN) store an intermediate result. The measure becomes readable, and each value is calculated once instead of every time it is referenced.
DIVIDE handles division by zero for you. Instead of an error or infinity, it returns blank, or an alternate result you choose.
Here is a year over year growth measure written the way production models are written: two variables, and DIVIDE for the ratio.
YoY % =
VAR CurrentSales = [Total Sales]
VAR PriorSales = CALCULATE ( [Total Sales],
SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
DIVIDE ( CurrentSales - PriorSales, PriorSales )Interviewers like this style. It shows you write DAX that someone else can read and maintain.
Question 7: Scenario - the total row does not equal the sum of the rows. Why, and how do you fix it?
This is the scenario managers love. Finance says the total in your table doesn't match the rows above it.
The rule is: pay a 10 percent bonus if a region sells more than 1000. Here is the first version of the measure and its result.
Bonus =
IF ( [Total Sales] > 1000, [Total Sales] * 0.1, 0 )| region | sales | bonus |
|---|---|---|
| East | 1030.00 | 103.00 |
| West | 890.00 | 0.00 |
| Total | 1920.00 | 192.00 |
Check this result yourself with SQL
The total row re-evaluates the IF on the grand total (1920), so it pays 192.00 even though only East qualifies.
WITH r AS (
SELECT c.region, SUM(f.qty * f.unit_price) AS sales
FROM fact_sales f JOIN dim_customer c ON c.customer_key = f.customer_key
GROUP BY c.region)
SELECT region, sales, bonus FROM (
SELECT region, sales, 0 AS srt,
CAST(CASE WHEN sales > 1000 THEN sales * 0.10 ELSE 0 END AS DECIMAL(10,2)) AS bonus
FROM r
UNION ALL
SELECT 'Total', SUM(sales), 1,
CAST(CASE WHEN SUM(sales) > 1000 THEN SUM(sales) * 0.10 ELSE 0 END AS DECIMAL(10,2))
FROM r) t
ORDER BY srt, region;East sold 1030.00, so it gets a bonus of 103.00. West sold 890.00, so it gets 0.00. But the total row shows 192.00, not 103.00.
The reason: a total is not the sum of the rows. It is the same measure, evaluated again with no region filter. With no filter, Total Sales is 1920.00, which is above 1000, so the IF pays 10 percent of 1920.00.
The fix is to iterate the regions with SUMX over VALUES of region, evaluate the rule for each one, and then add them up.
Bonus (fixed) =
SUMX ( VALUES ( Customer[Region] ),
IF ( [Total Sales] > 1000, [Total Sales] * 0.1, 0 ) )| bonus_total |
|---|
| 103.00 |
Check this result yourself with SQL
SUMX over the regions evaluates the rule per region, then sums: 103.00, the number finance expects.
WITH r AS (
SELECT c.region, SUM(f.qty * f.unit_price) AS sales
FROM fact_sales f JOIN dim_customer c ON c.customer_key = f.customer_key
GROUP BY c.region)
SELECT CAST(SUM(CASE WHEN sales > 1000 THEN sales * 0.10 ELSE 0 END) AS DECIMAL(10,2)) AS bonus_total
FROM r;Now the total is 103.00, the number finance expects.
In the interview, explain the cause first and the fix second. That shows the manager you understand filter context, not just DAX syntax.
Key points to remember
- Columns are stored per row; measures run per cell
- Row context points at a row; filter context filters
- CALCULATE changes filter context (and does context transition)
- SUMX iterates rows; never multiply two totals
- Totals are re-evaluated, not summed
The full SQL script
If you want to rebuild everything at once, here is the whole script: the sample data and the SQL check behind every result above.
Show the full SQL script (sample data and every check)
-- ========================================================================
-- Power BI Interview Q&A #5 - DAX fundamentals and filter context
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA pbi_q05; SET search_path TO pbi_q05;
-- SQL Server: write DATE '2026-06-01' as CAST('2026-06-01' AS DATE).
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_product (product_key INT PRIMARY KEY, product VARCHAR(30), category VARCHAR(20));
INSERT INTO dim_product VALUES
(1, 'Laptop Stand', 'Accessories'), (2, 'USB-C Cable', 'Accessories'),
(3, 'Monitor', 'Electronics'), (4, 'Keyboard', 'Electronics'),
(5, 'Desk Lamp', 'Home');
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, customer VARCHAR(30), city VARCHAR(20), region VARCHAR(10), email VARCHAR(40));
INSERT INTO dim_customer VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@tbshop.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@tbshop.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@tbshop.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@tbshop.com');
-- one row per day, 2025-01-01 .. 2026-12-31 (calendar year = fiscal year here)
CREATE TABLE dim_date (date DATE PRIMARY KEY, year INT, quarter VARCHAR(2), month_no INT, month VARCHAR(3));
INSERT INTO dim_date
SELECT d, EXTRACT(YEAR FROM d), 'Q' || EXTRACT(QUARTER FROM d), EXTRACT(MONTH FROM d), TO_CHAR(d, 'Mon')
FROM generate_series(DATE '2025-01-01', DATE '2026-12-31', INTERVAL '1 day') AS g(d);
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
order_date DATE REFERENCES dim_date(date),
product_key INT REFERENCES dim_product(product_key),
customer_key INT REFERENCES dim_customer(customer_key),
qty INT,
unit_price DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
( 1, '2025-01-10', 1, 1, 2, 40.00), ( 2, '2025-01-22', 3, 2, 1, 250.00),
( 3, '2025-02-05', 2, 3, 5, 10.00), ( 4, '2025-02-18', 5, 4, 1, 60.00),
( 5, '2025-03-03', 4, 1, 2, 50.00), ( 6, '2025-03-27', 3, 3, 1, 250.00),
( 7, '2026-01-08', 1, 2, 3, 40.00), ( 8, '2026-01-25', 3, 1, 1, 250.00),
( 9, '2026-02-11', 2, 4, 4, 10.00), (10, '2026-02-20', 4, 3, 2, 50.00),
(11, '2026-03-09', 5, 2, 2, 60.00), (12, '2026-03-30', 3, 4, 2, 250.00);
-- Step 1: Calculated column: Amount, evaluated row by row
SELECT f.sale_id, f.qty, f.unit_price, f.qty * f.unit_price AS amount
FROM fact_sales f
WHERE f.sale_id <= 6
ORDER BY f.sale_id;
-- Step 2: Measure: Total Sales by region (filter context)
SELECT c.region, SUM(f.qty * f.unit_price) AS total_sales
FROM fact_sales f JOIN dim_customer c ON c.customer_key = f.customer_key
GROUP BY c.region
ORDER BY c.region;
-- Step 3: CALCULATE: Electronics sales by region
SELECT c.region,
SUM(f.qty * f.unit_price) AS total_sales,
SUM(CASE WHEN p.category = 'Electronics' THEN f.qty * f.unit_price END) AS electronics_sales
FROM fact_sales f
JOIN dim_customer c ON c.customer_key = f.customer_key
JOIN dim_product p ON p.product_key = f.product_key
GROUP BY c.region
ORDER BY c.region;
-- Step 4: SUM of quantity times average price vs SUMX
SELECT CAST(SUM(qty) * AVG(unit_price) AS DECIMAL(10,2)) AS wrong_sum_x_avg,
SUM(qty * unit_price) AS right_sumx
FROM fact_sales;
-- Step 5: Percent of total with ALL / REMOVEFILTERS
SELECT p.category, SUM(f.qty * f.unit_price) AS total_sales,
CAST(100.0 * SUM(f.qty * f.unit_price) / (SELECT SUM(qty * unit_price) FROM fact_sales) AS DECIMAL(5,2)) AS pct_of_total
FROM fact_sales f JOIN dim_product p ON p.product_key = f.product_key
GROUP BY p.category
ORDER BY total_sales DESC;
-- Step 6: Bonus = 10 percent if a region sells more than 1000: rows vs total
WITH r AS (
SELECT c.region, SUM(f.qty * f.unit_price) AS sales
FROM fact_sales f JOIN dim_customer c ON c.customer_key = f.customer_key
GROUP BY c.region)
SELECT region, sales, bonus FROM (
SELECT region, sales, 0 AS srt,
CAST(CASE WHEN sales > 1000 THEN sales * 0.10 ELSE 0 END AS DECIMAL(10,2)) AS bonus
FROM r
UNION ALL
SELECT 'Total', SUM(sales), 1,
CAST(CASE WHEN SUM(sales) > 1000 THEN SUM(sales) * 0.10 ELSE 0 END AS DECIMAL(10,2))
FROM r) t
ORDER BY srt, region;
-- Step 7: Fix: iterate the regions with SUMX, then add up
WITH r AS (
SELECT c.region, SUM(f.qty * f.unit_price) AS sales
FROM fact_sales f JOIN dim_customer c ON c.customer_key = f.customer_key
GROUP BY c.region)
SELECT CAST(SUM(CASE WHEN sales > 1000 THEN sales * 0.10 ELSE 0 END) AS DECIMAL(10,2)) AS bonus_total
FROM r;
Part 6: Time Intelligence and Advanced DAX
The sample data on this page is invented for teaching. The result tables were produced by running the same calculation in SQL on PostgreSQL 16 over that sample data (open "Check this result yourself" under any result), so you can rebuild every number in Power BI Desktop and compare.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.