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.