How does RANKX work in a calculated column

[mp390988]

Hi,

I am trying to wrap my head around on how the RANKX function behaves in a calculated column.

For example, I have the below table where I have added a RANKX function inside a calculated column.

I know in a calcualated column there is a row context. So does that mean, the RANKX function will evalaute this formula row by row but it won't apply the filters coming from the current row as there is no context transition? But if that is the case, how does it know to put the rank of 1 against the rows that have FY=2025-2026 and rank of 2 against the rows that have FY=2024-2025.

Thanks

[burakkaragoz]

Hi @mp390988 ,

Great question. This is one of those DAX behaviors that can be a bit confusing at first.

You're right. Calculated columns operate in row context, and RANKX is one of the few functions that creates its own internal row context. In your case, you're ranking over the 'Dim Date' table using the 'FY' column.

So what happens is:

• For each row, RANKX evaluates the entire 'Dim Date' table.
• It ranks all the 'FY' values in descending order.
• Since multiple rows share the same 'FY', they all get the same rank (because you're using Dense ranking).

That’s why all rows with FY = 2025-2026 get rank 1, and those with FY = 2024-2025 get rank 2.

If you want to rank within a specific group (like per year or quarter), you’d need to use a FILTER inside RANKX to define that context.

Let me know if you want help adjusting the logic to rank within a group or based on another column.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[mp390988]

Hi @burakkaragoz ,

Thank you for your explanation but I still quite don't understand how it knows where to park the values. I know how it derives the values but not sure what the connection is for parking the values in the right place, if that makes sense. It's a bit like me finding my car spot. How does it know to put the rank of 1 against the rows that have FY=2025-2026 and rank of 2 against the rows that have FY=2024-2025 because there is no context transition so the financial years do not get passed down as filters.

[v-pbandela-msft · Accepted solution]

Hi @mp390988,

Thank you for reaching out in Microsoft Community Forum.

Thank you @burakkaragoz  for the helpful response.

Please follow below steps How RANKX Assigns Ranks in a Calculated Column;

Create a simple calendar table with Financial Year (FY) information.

Dim Date =
ADDCOLUMNS(
CALENDAR(DATE(2024, 1, 1), DATE(2026, 12, 31)),
"Month", FORMAT([Date], "MMM"),
"Month Sort", MONTH([Date]),
"Qtr", "Q" & FORMAT([Date], "Q"),
"FY", "FY " &
IF(MONTH([Date]) <= 3,
YEAR([Date]) - 1 & "-" & YEAR([Date]),
YEAR([Date]) & "-" & YEAR([Date]) + 1
)
)

Calculated column to rank the FYs.

FY Order =
RANKX(
'Dim Date',
'Dim Date'[FY],
,
DESC,
DENSE
)

Add a Table visual.

Drag the following fields into the visual:
- Date
- Month
- Qtr
- FY
- FY Order (the calculated column)

Please find the attached .pbix file for your reference. 

If this post helps in resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.

Regards,
Pavan.

[v-sshirivolu]

Hi @mp390988  ,

Just wanted to check if you had the opportunity to review the suggestion provided?

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-sshirivolu]

Hi @mp390988  ,

Just wanted to check if you had the opportunity to review the suggestion provided?

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-sshirivolu]

Hi @mp390988 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solution and give a 'Kudos'. This will be helpful for other community members who have similar problems to solve it faster.

Thank you