To *Bleep* with RANKX!

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