TOPN function in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Understanding TOPN with a single column

Given a Sales table with columns Product and SalesAmount, what does the following DAX expression return?

TOPN(3, Sales, Sales[SalesAmount], DESC)

Choose the correct description of the output.

Power BI

TOPN(3, Sales, Sales[SalesAmount], DESC)

A Returns the top 3 rows with the highest SalesAmount values from the Sales table.

B Returns all rows where SalesAmount is greater than 3.

C Returns the bottom 3 rows with the lowest SalesAmount values from the Sales table.

D Returns 3 random rows from the Sales table.

💡 Hint

TOPN returns a specified number of rows ordered by a column.

TOPN(3, Sales, Sales[SalesAmount], DESC) returns the top 3 rows sorted by SalesAmount in descending order, so the highest sales come first.

❓ visualization

intermediate

Visualizing TOPN results in a report

You want to create a bar chart in Power BI that shows the top 5 products by total sales. Which DAX measure would correctly filter the products to show only the top 5 in the chart?

A Top5Products = TOPN(5, Sales, Sales[SalesAmount], ASC)

B Top5Products = FILTER(VALUES(Sales[Product]), Sales[SalesAmount] > 5)

C Top5Products = CALCULATE(SUM(Sales[SalesAmount]), TOPN(5, Sales, Sales[SalesAmount], DESC))

D Top5Products = TOPN(5, VALUES(Sales[Product]), CALCULATE(SUM(Sales[SalesAmount])), DESC)

💡 Hint

Use TOPN with VALUES to get distinct products ordered by total sales.

Option D correctly uses TOPN to get the top 5 products by total sales amount. It uses VALUES to get distinct products and orders them by the sum of sales in descending order.

❓ data_modeling

advanced

Using TOPN with multiple sort columns

You have a Sales table with columns Product, Region, and SalesAmount. You want to get the top 3 sales rows ordered first by Region ascending, then by SalesAmount descending. Which DAX expression achieves this?

A TOPN(3, Sales, Sales[SalesAmount], DESC, Sales[Region], ASC)

B TOPN(3, Sales, Sales[Region], ASC, Sales[SalesAmount], DESC)

C TOPN(3, Sales, Sales[Region], DESC, Sales[SalesAmount], ASC)

D TOPN(3, Sales, Sales[SalesAmount], ASC, Sales[Region], DESC)

💡 Hint

The order of columns and their sort directions matter in TOPN.

Option B correctly orders by Region ascending first, then SalesAmount descending, matching the requirement.

🔧 Formula Fix

advanced

Identify the error in this TOPN usage

What error will the following DAX expression cause?

TOPN(5, Sales, Sales[SalesAmount])

Assuming Sales[SalesAmount] is numeric.

Power BI

TOPN(5, Sales, Sales[SalesAmount])

A Syntax error because the sort order (ASC or DESC) is missing.

B Returns bottom 5 rows instead of top 5.

C Runtime error because SalesAmount is numeric and cannot be sorted.

D No error; it returns top 5 rows sorted ascending by SalesAmount by default.

💡 Hint

TOPN requires explicit sort order for each sort column.

TOPN requires a sort order (ASC or DESC) for each sort column. Omitting it causes a syntax error.

🧠 Conceptual

expert

Scenario: Combining TOPN with CALCULATE and FILTER

You want to create a measure that calculates total sales for the top 3 products by sales amount, but only for the current year. Which DAX expression correctly achieves this?

A CALCULATE(SUM(Sales[SalesAmount]), FILTER(ALL(Sales), Sales[Product] IN TOPN(3, ALL(Sales[Product]), CALCULATE(SUM(Sales[SalesAmount])), DESC) && YEAR(Sales[Date]) = YEAR(TODAY())))

B CALCULATE(SUM(Sales[SalesAmount]), TOPN(3, ALL(Sales[Product]), CALCULATE(SUM(Sales[SalesAmount])), DESC), YEAR(Sales[Date]) = YEAR(TODAY()))

C CALCULATE(SUM(Sales[SalesAmount]), FILTER(ALL(Sales[Product]), Sales[Product] IN TOPN(3, ALL(Sales[Product]), CALCULATE(SUM(Sales[SalesAmount])), DESC) && YEAR(Sales[Date]) = YEAR(TODAY())))

D CALCULATE(SUM(Sales[SalesAmount]), FILTER(ALL(Sales[Product]), Sales[Product] IN TOPN(3, ALL(Sales[Product]), CALCULATE(SUM(Sales[SalesAmount])), DESC)), YEAR(Sales[Date]) = YEAR(TODAY()))

💡 Hint

Use FILTER with ALL on Product and include the year filter inside FILTER.

Option C correctly filters all products to top 3 by sales, and applies the year filter inside the FILTER function to limit rows to current year before summing sales.