Calculated tables in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a calculated table that lists all unique product categories.

Power BI

ProductCategories = DISTINCT([1])

Drag options to blanks, or click blank then click option'

ASUM(Products[Category])

BProducts

CFILTER(Products, Products[Category])

DProducts[Category]

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM instead of DISTINCT

Passing the whole table instead of a column

The DISTINCT function returns unique values from the specified column. Here, Products[Category] is the correct column to get unique categories.

2fill in blank

medium

Complete the code to create a calculated table that filters sales for the year 2023.

Power BI

Sales2023 = FILTER(Sales, Sales[Year] [1] 2023)

Drag options to blanks, or click blank then click option'

A>

B=

C<

D>=

Attempts:

3 left

💡 Hint

Common Mistakes

Using > or < which filters wrong years

Using >= which includes 2023 and later years

The FILTER function needs a condition. To get sales only for 2023, use Sales[Year] = 2023.

3fill in blank

hard

Fix the error in the calculated table that tries to sum sales by product.

Power BI

SalesByProduct = SUMMARIZE(Sales, Sales[Product], "TotalSales", [1](Sales[Amount]))

Drag options to blanks, or click blank then click option'

ASUM

BSUMMARIZE

CFILTER

DDISTINCT

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUMMARIZE inside itself incorrectly

Using FILTER or DISTINCT which don't aggregate

SUMMARIZE groups data, but to calculate total sales, use SUM on the Amount column.

4fill in blank

hard

Fill both blanks to create a calculated table that lists customers with sales greater than 1000.

Power BI

HighValueCustomers = FILTER([1], [2] > 1000)

Drag options to blanks, or click blank then click option'

ACustomers

BSales[Amount]

CCustomers[TotalSales]

DSales

Attempts:

3 left

💡 Hint

Common Mistakes

Filtering Sales table instead of Customers

Using Sales[Amount] which is per sale, not per customer

We filter the Customers table where their TotalSales column is greater than 1000.

5fill in blank

hard

Fill all three blanks to create a calculated table that summarizes total sales by region and year.

Power BI

SalesSummary = SUMMARIZE([1], [2], [3], "TotalSales", SUM(Sales[Amount]))

Drag options to blanks, or click blank then click option'

ASales

BSales[Region]

CSales[Year]

DCustomers

Attempts:

3 left

💡 Hint

Common Mistakes

Using Customers table instead of Sales

Grouping by wrong columns

SUMMARIZE groups the Sales table by Region and Year, then sums Amount as TotalSales.

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