To *Bleep* with RANKX! 04-22-2020 14:14 PM - last edited 04-23-2020 22:48 PM

[Greg_Deckler]

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

[Kniggit270]

@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

[glissando]

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

[Greg_Deckler]

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