Calculated tables in Power BI - Cell-by-Cell Formula Trace

Sample Data

This table lists products with their IDs, names, and categories.

Cell	Value
A1	ProductID
B1	ProductName
C1	Category
A2	1
B2	Apple
C2	Fruit
A3	2
B3	Carrot
C3	Vegetable
A4	3
B4	Banana
C4	Fruit
A5	4
B5	Broccoli
C5	Vegetable

Formula Trace

FILTER(Products, Products[Category] = "Fruit")

Step 1: Products[Category]

Step 2: Products[Category] = "Fruit"

Step 3: FILTER(Products, [TRUE, FALSE, TRUE, FALSE])

Cell Reference Map

    A          B           C
1 ProductID ProductName  Category
2    1       Apple       Fruit  <-- included
3    2       Carrot     Vegetable
4    3       Banana      Fruit  <-- included
5    4       Broccoli   Vegetable

The formula filters rows based on the Category column values in column C.

Result

    A          B           C
1 ProductID ProductName  Category
2    1       Apple       Fruit
4    3       Banana      Fruit

The calculated table shows only products where Category is 'Fruit'.

Sheet Trace Quiz - 3 Questions

What does the FILTER function do in this formula?

A Keeps only rows where Category is 'Fruit'

B Deletes rows where Category is 'Fruit'

C Changes all categories to 'Fruit'

D Adds a new column named 'Fruit'

Key Result

FILTER(table, condition) returns a table with rows where condition is TRUE

Practice

1. What is the main purpose of a calculated table in Power BI?

easy

A. To create a new table based on a DAX formula without altering original data

B. To directly edit the source data in the database

C. To visualize data using charts and graphs

D. To export data to Excel automatically

Solution

1. Step 1: Understand calculated tables

   Calculated tables are created using DAX formulas inside Power BI to generate new tables.

2. Step 2: Compare with other options

   They do not edit source data, create visuals, or export data directly.

3. Final Answer:

   To create a new table based on a DAX formula without altering original data -> Option A

4. Quick Check:

   Calculated tables = new tables from DAX [OK]

Hint: Calculated tables create new data, not change original [OK]

Common Mistakes:

• Confusing calculated tables with data editing
• Thinking calculated tables create visuals
• Assuming calculated tables export data

2. Which of the following is the correct syntax to create a calculated table named TopProducts showing top 5 products by sales?

easy

A. TopProducts = TOPN(5, Products, Products[Sales], DESC)

B. TopProducts = FILTER(Products, Products[Sales] > 5)

C. TopProducts = SUMMARIZE(Products, Products[ProductName])

D. TopProducts = CALCULATE(Products, TOPN(5))

Solution

1. Step 1: Identify correct DAX function for top rows

   TOPN returns the top N rows based on a column, here top 5 by sales.

2. Step 2: Check syntax correctness

   TopProducts = TOPN(5, Products, Products[Sales], DESC) uses TOPN(5, Products, Products[Sales], DESC) which is correct syntax for top 5 products by sales.

3. Final Answer:

   TopProducts = TOPN(5, Products, Products[Sales], DESC) -> Option A

4. Quick Check:

   TOPN syntax = TopProducts = TOPN(5, Products, Products[Sales], DESC) [OK]

Hint: Use TOPN for top rows with order [OK]

Common Mistakes:

• Using FILTER instead of TOPN for top rows
• Incorrect CALCULATE usage for tables
• Missing order direction in TOPN

3. Given the calculated table:

HighSales = FILTER(Sales, Sales[Amount] > 1000)

What will HighSales contain?

medium

A. Sum of sales Amount greater than 1000

B. All sales records where Amount is less than 1000

C. Error because FILTER cannot be used in calculated tables

D. All sales records where Amount is greater than 1000

Solution

1. Step 1: Understand FILTER function in calculated tables

   FILTER returns a table with rows meeting the condition, here Amount > 1000.

2. Step 2: Analyze the condition

   It keeps only sales records where Amount is greater than 1000.

3. Final Answer:

   All sales records where Amount is greater than 1000 -> Option D

4. Quick Check:

   FILTER with condition = filtered rows [OK]

Hint: FILTER returns rows matching condition [OK]

Common Mistakes:

• Thinking FILTER returns aggregated values
• Confusing greater than with less than
• Believing FILTER cannot create tables

4. You wrote this calculated table:

RecentSales = FILTER(Sales, Sales[Date] >= TODAY() - 30)

But it gives an error. What is the likely problem?

medium

A. Sales table cannot be filtered in calculated tables

B. FILTER requires aggregation functions inside

C. TODAY() cannot be used in calculated tables because it is dynamic

D. The date comparison operator >= is invalid in DAX

Solution

1. Step 1: Understand TODAY() in calculated tables

   TODAY() is dynamic and calculated tables are static, so TODAY() causes error.

2. Step 2: Check other options

   FILTER works without aggregation, >= is valid, and Sales can be filtered.

3. Final Answer:

   TODAY() cannot be used in calculated tables because it is dynamic -> Option C

4. Quick Check:

   Dynamic functions disallowed in calculated tables [OK]

Hint: Avoid dynamic functions like TODAY() in calculated tables [OK]

Common Mistakes:

• Thinking FILTER needs aggregation
• Misunderstanding comparison operators
• Assuming all functions work in calculated tables

5. You want to create a calculated table CustomerSalesSummary that shows each customer and their total sales amount, but only for customers with sales above 5000. Which DAX expression correctly achieves this?

hard

A. CustomerSalesSummary = SUMMARIZE(Sales, Sales[CustomerID], "TotalSales", FILTER(Sales[Amount] > 5000))

B. CustomerSalesSummary = FILTER(SUMMARIZE(Sales, Sales[CustomerID], "TotalSales", SUM(Sales[Amount])), [TotalSales] > 5000)

C. CustomerSalesSummary = CALCULATETABLE(Sales, Sales[Amount] > 5000)

D. CustomerSalesSummary = SUMMARIZE(Sales, Sales[CustomerID], "TotalSales", SUM(Sales[Amount])) WHERE TotalSales > 5000

Solution

1. Step 1: Summarize sales by customer

   SUMMARIZE groups Sales by CustomerID and calculates total sales per customer.

2. Step 2: Filter summarized table by total sales > 5000

   FILTER applies condition on the summarized table's TotalSales column.

3. Step 3: Check syntax correctness

   CustomerSalesSummary = FILTER(SUMMARIZE(Sales, Sales[CustomerID], "TotalSales", SUM(Sales[Amount])), [TotalSales] > 5000) correctly nests FILTER around SUMMARIZE with proper syntax.

4. Final Answer:

   CustomerSalesSummary = FILTER(SUMMARIZE(Sales, Sales[CustomerID], "TotalSales", SUM(Sales[Amount])), [TotalSales] > 5000) -> Option B

5. Quick Check:

   SUMMARIZE then FILTER = CustomerSalesSummary = FILTER(SUMMARIZE(Sales, Sales[CustomerID], "TotalSales", SUM(Sales[Amount])), [TotalSales] > 5000) [OK]

Hint: Use SUMMARIZE then FILTER for grouped filtered tables [OK]

Common Mistakes:

• Placing FILTER inside SUMMARIZE incorrectly
• Using WHERE clause which is invalid in DAX
• Applying CALCULATETABLE without grouping