topic Re: Rank function - some clarification needed in DAX Commands and Tips

Rank function - some clarification needed

mp390988 — Mon, 17 Mar 2025 22:10:25 GMT

Hi,

I have a measure called BoxOffice which is simply SUM((Film[Box Office Dollars])

I want to understand why the Rank function yields different results when I write [BoxOffice] versus SUM(Film[Box Office Dollars]) as you can see in results below. My understanding is that for context transition to take place, we need an iterator and a measure inside the iterator. Rank is not an iterator so there is no row context.

Re: Rank function - some clarification needed

Deku — Mon, 17 Mar 2025 22:48:57 GMT

A measure has a implict Calculate() wrapped around it causing context transition, changing the row context of the table you are iterating with rank to be convert to a filter context.

Where as the sum by itself only has the row context from the iteration

Re: Rank function - some clarification needed

mp390988 — Mon, 17 Mar 2025 22:54:45 GMT

I understand a measure has an implicit Calculate wrapped around it which can cause context transition provided a table is being iterated. My question is that all iterator functions in DAX have an X at the end of it, like SUMX, AVERAGEX, RANKX ---> these are iterators but there is an exception, FILTER is also an iterator which doesn't have an "X". Does the same apply to RANK? Is the RANK function an iterator just like FILTER that doesn't have an "X" in its name?

Re: Rank function - some clarification needed

Deku — Mon, 17 Mar 2025 23:03:52 GMT

RANKX is a iterator function.

Rank is a window functions. It has to generate a data cache (a table) that has all the columns from the relation parameter you provide. For each row in that table it will apply your orderby. If the orderby is a expression then it will have to be evaluated similar to a x function

Re: Rank function - some clarification needed

v-saisrao-msft — Mon, 14 Apr 2025 15:29:40 GMT

Hi mp390988,
I wanted to check if you had the opportunity to review the information provided by Deku. Please feel free to contact us if you have any further questions. 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.

Re: Rank function - some clarification needed

v-saisrao-msft — Thu, 17 Apr 2025 06:24:59 GMT

Hi mp390988,

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

Thank you.

Re: Rank function - some clarification needed

v-saisrao-msft — Wed, 23 Apr 2025 06:27:08 GMT

Hi mp390988,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.