Calculated tables in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate a table with unique customers

Given a Sales table with multiple rows per customer, which DAX expression creates a calculated table with unique Customer IDs?

Power BI

Sales = DATATABLE(
    "CustomerID", INTEGER,
    { {1}, {2}, {1}, {3}, {2} }
)

-- Which calculated table expression returns unique CustomerIDs?

A UniqueCustomers = FILTER(Sales, Sales[CustomerID] <> BLANK())

B UniqueCustomers = DISTINCT(Sales[CustomerID])

C UniqueCustomers = VALUES(Sales[CustomerID])

D UniqueCustomers = ALL(Sales[CustomerID])

💡 Hint

Think about which function returns a table of unique values from a column.

DISTINCT returns a table with unique values from the specified column. VALUES can return unique values but also includes BLANKs and behaves differently with filters. FILTER just filters rows but does not remove duplicates. ALL removes filters but does not guarantee uniqueness.

❓ visualization

intermediate

Best visualization for a calculated table showing monthly sales totals

You created a calculated table with monthly sales totals per product. Which visualization best shows trends over time for multiple products?

A Stacked column chart with months on X-axis and sales on Y-axis

B Table visual listing months and sales values

C Line chart with months on X-axis and sales on Y-axis, one line per product

D Pie chart showing total sales per product for all months combined

💡 Hint

Think about which chart type best shows trends over time for multiple categories.

A line chart is best to show trends over time for multiple products because it clearly shows changes month by month with separate lines. Stacked columns can be harder to compare trends. Pie charts do not show time trends. Tables show raw data but not trends visually.

❓ data_modeling

advanced

Creating a calculated table to summarize sales by region and year

You have a Sales table with columns: Region, Year, and SalesAmount. Which DAX expression creates a calculated table summarizing total sales by Region and Year?

A Summary = SUMMARIZE(Sales, Sales[Region], Sales[Year], "TotalSales", SUM(Sales[SalesAmount]))

B Summary = GROUPBY(Sales, Sales[Region], Sales[Year], "TotalSales", SUMX(CURRENTGROUP(), Sales[SalesAmount]))

C Summary = ADDCOLUMNS(Sales, "TotalSales", SUM(Sales[SalesAmount]))

D Summary = FILTER(Sales, Sales[SalesAmount] > 0)

💡 Hint

Consider which function groups rows and calculates aggregations in a new table.

SUMMARIZE groups the Sales table by Region and Year and adds a new column with the sum of SalesAmount. GROUPBY can do similar but requires SUMX with CURRENTGROUP for aggregation. ADDCOLUMNS adds columns but does not group rows. FILTER only filters rows and does not summarize.

🔧 Formula Fix

advanced

Identify the error in this calculated table expression

What error will this DAX calculated table expression cause? NewTable = FILTER(Sales, Sales[Quantity] > 10, Sales[Price] < 100)

Power BI

NewTable = FILTER(Sales, Sales[Quantity] > 10, Sales[Price] < 100)

A SyntaxError: FILTER only accepts two arguments

B No error, returns filtered table with Quantity > 10 and Price < 100

C TypeError: Sales[Price] is not a valid column

D RuntimeError: FILTER cannot be used in calculated tables

💡 Hint

Check the number of arguments FILTER accepts.

FILTER function accepts exactly two arguments: a table and a single filter expression. Passing two separate filter expressions as separate arguments causes a syntax error. To combine conditions, use && inside one filter expression.

🧠 Conceptual

expert

Understanding row context in calculated tables

In a calculated table expression using ADDCOLUMNS over a Sales table, you add a column with the expression: TotalCustomerSales = CALCULATE(SUM(Sales[SalesAmount]), ALLEXCEPT(Sales, Sales[CustomerID])) What does this expression compute for each row in the new table?

A Total sales for all customers except the current CustomerID

B Total sales for the current row only

C Total sales ignoring all filters including CustomerID

D Total sales for the current CustomerID across all other filters

💡 Hint

Think about what ALLEXCEPT does inside CALCULATE.

ALLEXCEPT removes all filters except the one on CustomerID, so CALCULATE sums SalesAmount for the current CustomerID ignoring other filters. This gives total sales per customer across the dataset.

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