topic Re: Understanding the RankX in DAX Commands and Tips

Understanding the RankX

mp390988 — Thu, 20 Mar 2025 23:39:13 GMT

I noticed when I replace the table ALL(DimProduct) with ALL('DimProduct'[Colour]) inside my RankX function it yields different results. I don't quite understand. I know the ranking results are incorrect with ALL('DimProduct']) and correct with ALL('DimProduct'[Colour]) but my understanding is that ALL('DimProduct'[Colour]) removes the filter context from the visual (i.e. the colour) but I thought ALL(DimProduct) removes all filters inlcuding colour so I would have expected them to give same results.

RankX with ALL(DimProduct)

RankX with ALL('DimProduct'[Colour])

I added a calculated column to my DimProducts table to calculate the SALES AMOUNT.
Here is the link to the excel file containing all the records from DimProducts with SALES AMOUNT - here

I don't know how the rank with ALL(DimProducts) is ranking colour BLACK with SALES AMOUNT = 8,838,411.96 as 1 and then colour BLUE with SALES AMOUNT = 2,279,096.28 as 1 also. Doesn't make sense.

Re: Understanding the RankX

Deku — Thu, 20 Mar 2025 23:44:02 GMT

RANKX iterates the table you provide, calculates the expression as a extra column, and ranks with that new column

With all(DimProduct) you are ranking on the entire table. With all(DimProduct[colour]) you are ranking on only a list of the distinct colours,

Re: Understanding the RankX

mp390988 — Fri, 21 Mar 2025 08:05:31 GMT

Ok so I guess the question I have is how does the ranking on the whole table work ? Because I don't get the same ranks when I try to work it out manually.

Re: Understanding the RankX

Deku — Fri, 21 Mar 2025 08:18:00 GMT

Try adding each field from dimproduct into a table with sales amount. I assume you'll see a combination where blue, black, yellow, red and silver all have the same top amount.

Re: Understanding the RankX

mp390988 — Fri, 21 Mar 2025 08:45:17 GMT

Will that not be the excel file I added as a link in my original post? That excel file is the whole of DimProduct table with a calculated sales amount column right at the end.

Re: Understanding the RankX

v-dineshya — Tue, 06 May 2025 10:52:42 GMT

HI mp390988 ,

Thank you for reaching out to the Microsoft Community Forum.

Key Differences:

ALL('DimProduct'[Color]):

1.This removes only the filter on the [Color] column, meaning the ranking is done at the Color level.

2.Other filters from the DimProduct table (e.g., size, product, etc.) remain intact if applied in the visual.

3.This is what you want if you're ranking aggregated Sales Amount per Color, and it gives the correct ranks one rank per color.

ALL(DimProduct):

1.This removes all filters on the DimProduct table, including Color, Size, ProductKey, etc.

2.As a result, you're ranking individual products (rows in the table), not aggregated color values.

3.When the visual is grouped by Color, but you're ranking by a row-level context, it causes multiple products under the same color to get ranked leading to odd behavior like multiple ranks of 1.

Note: You're calculating rank across all products, not across distinct colors. So even though the visual groups by Color, the rank measure is still working row-by-row behind the scenes, which is misleading in an aggregated visual.

That's why:

Black with $8.8M is ranked 1 (correct as a color total),

But Blue, Red, Silver, etc., also get rank 1 — because the RANKX is being evaluated at the product level within those colors.

Rank Sales Amount =
RANKX(
ALL('DimProduct'[Color]),
[Sales Amount],
,
,
DENSE
)

Note: This ensures you're ranking over the distinct list of colors, using their aggregated Sales Amount, which aligns with how your visual is grouped.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

Re: Understanding the RankX

v-dineshya — Mon, 12 May 2025 04:13:45 GMT

Hi mp390988 ,

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

Re: Understanding the RankX

v-dineshya — Thu, 15 May 2025 15:28:42 GMT

Hi @mp390988 ,

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

Re: Understanding the RankX

v-dineshya — Mon, 19 May 2025 15:38:00 GMT

Hi @mp390988 ,

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you