RANKX for ranking in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate rank of sales by product

Given a Sales table with columns Product and SalesAmount, what is the rank of Product 'B' using this measure?

Rank Sales = RANKX(ALL(Sales[Product]), SUM(Sales[SalesAmount]), , DESC, Dense)

Sales data:
Product A: 100
Product B: 200
Product C: 150

Power BI

Rank Sales = RANKX(ALL(Sales[Product]), SUM(Sales[SalesAmount]), , DESC, DENSE)

A 3

B 2

C 1

D Error

💡 Hint

Remember that DESC means highest value gets rank 1.

Product B has the highest sales (200), so it gets rank 1 with DESC order and Dense ranking.

❓ visualization

intermediate

Best visualization for showing product ranks

You want to show product sales ranks on a dashboard. Which visualization best shows the rank order clearly?

A Bar chart sorted by rank ascending

B Pie chart showing sales amounts

C Scatter plot with sales on X and product name on Y

D Table with product names only

💡 Hint

Think about which chart shows order clearly.

A bar chart sorted by rank ascending shows products from best to worst rank clearly and is easy to read.

❓ data_modeling

advanced

Ranking with filters and context

You have a Sales table with Product, Region, and SalesAmount. You want to rank products by sales within each Region. Which DAX measure correctly calculates this?

A Rank by Region = RANKX(ALL(Sales[Product]), CALCULATE(SUM(Sales[SalesAmount]), ALLEXCEPT(Sales, Sales[Region])), , DESC, DENSE)

B Rank by Region = RANKX(ALL(Sales[Product], Sales[Region]), SUM(Sales[SalesAmount]), , DESC, DENSE)

C Rank by Region = RANKX(ALLSELECTED(Sales[Product]), CALCULATE(SUM(Sales[SalesAmount])), , DESC, DENSE)

D Rank by Region = RANKX(ALL(Sales[Product]), SUM(Sales[SalesAmount]), , DESC, DENSE)

💡 Hint

You want to keep Region filter but remove Product filter inside RANKX.

Option A removes Product filter but keeps Region filter using ALLEXCEPT, so ranking is within each Region.

🔧 Formula Fix

advanced

Identify error in RANKX measure

This DAX measure is intended to rank products by sales but returns an error:

Rank Sales = RANKX(Sales[Product], SUM(Sales[SalesAmount]))

What is the cause of the error?

Power BI

Rank Sales = RANKX(Sales[Product], SUM(Sales[SalesAmount]))

A Missing order argument DESC or ASC

B SUM cannot be used inside RANKX

C RANKX requires a filter argument

D RANKX expects a table as first argument, but Sales[Product] is a column, not a table

💡 Hint

Check the first argument type for RANKX.

RANKX requires a table as first argument, but Sales[Product] is a column reference, causing error.

🧠 Conceptual

expert

Effect of ranking method on ties

You use RANKX with the Dense ranking method on sales data with ties. How does Dense ranking handle ties compared to Skip ranking?

A Dense ranking skips ranks after ties, next rank increments by number of tied items

B Dense ranking assigns the same rank to ties and next rank increments by 1

C Dense ranking assigns unique ranks ignoring ties

D Dense ranking always assigns rank 1 to all tied items

💡 Hint

Think about how ranks progress after ties in Dense vs Skip.

Dense ranking assigns the same rank to tied values and the next rank increments by 1, unlike Skip which skips ranks.