Calculated tables in Power BI - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a calculated table in Power BI?

A calculated table is a table created using a DAX formula inside Power BI. It is not imported from data but generated based on other data or calculations.

beginner

How do you create a calculated table in Power BI?

You create a calculated table by going to the Modeling tab and selecting 'New Table', then writing a DAX formula that defines the table's data.

intermediate

Which DAX function can you use to create a calculated table that combines rows from two tables?

You can use the UNION() function to combine rows from two tables into a calculated table.

beginner

True or False: Calculated tables update automatically when the data model refreshes.

True. Calculated tables recalculate and update automatically when the data model refreshes or when dependent data changes.

intermediate

What is a practical use case for calculated tables?

Calculated tables can be used to create summary tables, filter data, or create relationships that don't exist in the original data.

Which tab in Power BI do you use to create a calculated table?

A Home

B Modeling

C View

D Data

Calculated tables are created from the Modeling tab by selecting 'New Table'.

What does the DAX function UNION() do when used in a calculated table?

A Filters rows based on a condition

B Joins two tables side by side

C Combines rows from two tables into one

D Calculates the sum of a column

UNION() stacks rows from two tables into a single table.

Which of these is NOT true about calculated tables?

A They are imported directly from external files

B They can be used to create new relationships

C They update when the data model refreshes

D They can be created using DAX formulas

Calculated tables are generated inside Power BI using DAX, not imported from files.

If you want to create a table that only shows sales from 2023, which DAX function might you use in a calculated table?

A SUM()

B AVERAGE()

C UNION()

D FILTER()

FILTER() lets you create a table with rows that meet a condition, like sales from 2023.

What happens to a calculated table when you refresh your Power BI data model?

A It recalculates based on current data

B It deletes automatically

C It stays the same until manually updated

D It exports to Excel

Calculated tables recalculate automatically when the data model refreshes.

Explain what a calculated table is and how it differs from imported tables in Power BI.

Think about how you create it and when it changes.

Describe two practical examples where you might use calculated tables in a Power BI report.

Consider why you might want new tables beyond your original data.

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