To Bleep with RANKX!

Greg_Deckler — Fri, 24 Apr 2020 05:48:29 GMT

Perhaps no other function in the DAX language has caused more grief and strife than RANKX (other than perhaps the entire suite of time "intelligence" functions). Not a day goes by in the forums that there are not multiple questions about how RANKX works. And there are pages upon pages upon pages of blog articles explaining RANKX's intricacies. So, similar to Time Intelligence the Hard Way, the question we must ask ourselves is, why bother? Does RANKX just exist to give bloggers something to write about? In reality, the RANKX function really doesn't do anything that can't be done with base DAX functions with much less arcane syntax and hoops to jump through. So, to *bleep* with RANKX! We don't need it!

A simple column Rank can be achieved using COUNTROWS:

ToHellWithRankXDesc = COUNTROWS(FILTER('Table',[Value]>=EARLIER([Value]))) ToHellWithRankXAsc = COUNTROWS(FILTER('Table',[Value]<=EARLIER([Value])))

The equivalent columns in RANKX are:

RankXDesc = RANKX('Table',[Value],,DESC) RankXAsc = RANKX('Table',[Value],,ASC)

We can also use measures to achieve rankings much more intuitively by any combination of categories and subcategories:

ToHellWithRankXDescMeasure = COUNTROWS( FILTER( ALL('Table'), [Value]>=SUM([Value]) ) ) ToHellWithRankXDescMeasure2 = COUNTROWS( FILTER( SUMMARIZE( ALL('Table'), [Group], "Value",SUM('Table'[Value]) ), [Value]>=SUM([Value]) ) ) ToHellWithRankXDescMeasure3 = COUNTROWS( FILTER( SUMMARIZE( FILTER( ALL('Table'), [Group] = MAX([Group]) ), [Item], "Value",SUM('Table'[Value]) ), [Value]>=SUM([Value]) ) )

Simple, logical, no crazy putting CALCULATE in places that do not make intuitive sense, remembering which parameter you probably don't want to ever use (it's the third one), remembering some hokey key word like ASC and DESC or struggling with how many different columns to wrap an ALL around; just plain old standard table filtering...

NOTE: The answer to the above speculation and rant is that RANKX is actually highly performant compared to using COUNTROWS and FILTER. You try ranking 100,000 rows using COUNTROWS and FILTER and you will likely be very sad. But who ranks that many rows anyway? 🙂

Here are the equivalent RANKX measures:

RankXDescMeasure = RANKX( ALL('Table'), CALCULATE( SUM('Table'[Value]) ), , DESC ) RankXDescMeasure2 = RANKX( ALL('Table'[Group]), CALCULATE( SUM('Table'[Value]) ), , DESC ) RankXDescMeasure3 = RANKX( FILTER( ALL( 'Table'[Group], 'Table'[Item] ), 'Table'[Group] = MAX('Table'[Group]) ), CALCULATE(SUM('Table'[Value])) , ,DESC )

eyJrIjoiMWEzNWQ0MGUtNGQ2Ny00NDdiLWJmYTMtZTU0ZjZkNThmZDU0IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Re: To Bleep with RANKX!

Anonymous — Mon, 27 Apr 2020 01:16:44 GMT

Doesn't give the same results as RANKX when there are ties. Or am I missing something? Thanks

Person	Total Calls	ToHellWithRankXDesc	RANKX
B	220	1	1
A	160	3	2
C	160	3	2

Re: To Bleep with RANKX!

Greg_Deckler — Tue, 05 May 2020 04:33:59 GMT

Depends on the parameters you provide to RANKX but definitely solvable.

Re: To Bleep with RANKX!

Kniggit270 — Thu, 27 Apr 2023 08:40:24 GMT

Greg_Deckler , I'm having trouble with getting this to work (specifically the last formula).

Instead of the rank, I'm simply getting a count of all the distinct groups that have sales for a particular product.

Have logged a post here: https://community.powerbi.com/t5/Desktop/Summarized-Ranking-with-RLS/td-p/3204959

Would really appreciate some help.

Many thanks

topic Re: To *Bleep* with RANKX! in Quick Measures Gallery

To *Bleep* with RANKX!

Re: To *Bleep* with RANKX!

Re: To *Bleep* with RANKX!

Re: To *Bleep* with RANKX!

topic Re: To Bleep with RANKX! in Quick Measures Gallery

To Bleep with RANKX!

Re: To Bleep with RANKX!

Re: To Bleep with RANKX!

Re: To Bleep with RANKX!