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.

Practice

1. What does the TOPN function do in Power BI?

easy

A. Joins two tables based on a key column.

B. Calculates the sum of a column in a table.

C. Filters rows where a condition is true.

D. Returns the top N rows from a table based on a specified column's values.

Solution

1. Step 1: Understand the purpose of TOPN

   TOPN is designed to return a subset of rows from a table, specifically the top N rows sorted by a column.

2. Step 2: Compare with other functions

   Functions like SUM aggregate values, FILTER filters rows by condition, and JOIN merges tables. TOPN specifically returns top rows.

3. Final Answer:

   Returns the top N rows from a table based on a specified column's values. -> Option D

4. Quick Check:

   TOPN = returns top rows [OK]

Hint: TOPN picks top rows by sorting a column [OK]

Common Mistakes:

• Confusing TOPN with aggregation functions like SUM
• Thinking TOPN filters by condition instead of top values
• Mixing TOPN with table joins

2. Which of the following is the correct syntax for the TOPN function in DAX?

easy

A. TOPN(<number_of_rows>, <table>, <order_by_column>, <order>)

B. TOPN(<table>, <number_of_rows>, <order_by_column>, <order>)

C. TOPN(<number_of_rows>, <order_by_column>, <table>, <order>)

D. TOPN(<order_by_column>, <number_of_rows>, <table>, <order>)

Solution

1. Step 1: Recall TOPN function syntax

   The correct syntax is TOPN(number_of_rows, table, order_by_column, order). The first argument is how many rows to return.

2. Step 2: Verify argument order

   Number of rows comes first, then the table, then the column to sort by, and finally the sort order (ASC or DESC).

3. Final Answer:

   TOPN(<number_of_rows>, <table>, <order_by_column>, <order>) -> Option A

4. Quick Check:

   Syntax = TOPN(number_of_rows, table, column, order) [OK]

Hint: Number of rows is always first in TOPN syntax [OK]

Common Mistakes:

• Swapping table and number_of_rows arguments
• Placing order_by_column before table
• Omitting the order argument

3. Given the table Sales with columns Product and Revenue, what does this DAX expression return?

TOPN(2, Sales, Sales[Revenue], DESC)

medium

A. The two products with the highest revenue.

B. The two products with the lowest revenue.

C. All products sorted by revenue descending.

D. An error because TOPN needs ASC order.

Solution

1. Step 1: Analyze the TOPN parameters

   TOPN(2, Sales, Sales[Revenue], DESC) means return 2 rows from Sales table sorted by Revenue in descending order.

2. Step 2: Understand sorting order effect

   Descending order means highest values come first, so it returns top 2 products with highest revenue.

3. Final Answer:

   The two products with the highest revenue. -> Option A

4. Quick Check:

   TOPN with DESC = top highest values [OK]

Hint: DESC means highest values first in TOPN [OK]

Common Mistakes:

• Confusing DESC with ASC order
• Thinking TOPN returns all rows sorted
• Assuming TOPN returns lowest values by default

4. Identify the error in this DAX expression:

TOPN(3, Sales)

medium

A. Table name should be in quotes.

B. The number of rows should be last argument.

C. Missing the order_by_column argument.

D. Revenue column cannot be used in TOPN.

Solution

1. Step 1: Check TOPN function arguments

   TOPN requires (at minimum) three arguments: number_of_rows, table, order_by_column [, order (ASC or DESC)].

2. Step 2: Identify missing argument

   The expression lacks the order_by_column argument, which is mandatory to specify the column for sorting.

3. Final Answer:

   Missing the order_by_column argument. -> Option C

4. Quick Check:

   TOPN needs order_by_column [OK]

Hint: Always include order_by_column in TOPN [OK]

Common Mistakes:

• Omitting order_by_column argument
• Putting number_of_rows last
• Using quotes around table names

5. You want to create a measure that shows the total revenue of the top 3 products by revenue. Which DAX expression correctly uses TOPN to achieve this?

hard

A. CALCULATE(SUM(Sales[Revenue]), TOPN(3, Sales, DESC))

B. SUMX(TOPN(3, Sales, Sales[Revenue], DESC), Sales[Revenue])

C. SUMX(FILTER(Sales, Sales[Revenue] >= TOPN(3, Sales, Sales[Revenue], DESC)), Sales[Revenue])

D. CALCULATE(TOPN(3, Sales, Sales[Revenue], DESC), SUM(Sales[Revenue]))

Solution

1. Step 1: Understand the goal

   You want to sum revenue only for the top 3 products by revenue.

2. Step 2: Analyze each option

   CALCULATE(SUM(Sales[Revenue]), TOPN(3, Sales, DESC)) has invalid TOPN syntax (missing order_by_column). SUMX(TOPN(3, Sales, Sales[Revenue], DESC), Sales[Revenue]) uses SUMX over TOPN table, correctly summing revenue of top 3 rows. SUMX(FILTER(Sales, Sales[Revenue] >= TOPN(3, Sales, Sales[Revenue], DESC)), Sales[Revenue]) misuses FILTER with TOPN inside condition, which is invalid. CALCULATE(TOPN(3, Sales, Sales[Revenue], DESC), SUM(Sales[Revenue])) misplaces SUM inside CALCULATE with TOPN, causing syntax error.

3. Final Answer:

   SUMX(TOPN(3, Sales, Sales[Revenue], DESC), Sales[Revenue]) -> Option B

4. Quick Check:

   SUMX over TOPN table sums top rows [OK]

Hint: Use SUMX over TOPN to sum top rows [OK]

Common Mistakes:

• Using TOPN directly inside CALCULATE filter
• Misusing FILTER with TOPN in condition
• Putting aggregation inside CALCULATE incorrectly