skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Microsoft 365 + Power BI
    • Dynamics 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Partners Overview
    • Solutions Partners
    • BI Specialized Partners
    • Power BI CSOs
    • Fabric Partner Community
    • Training
    • Getting started
      • Overview
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Power BI forums
    • Updates
    • News & Announcements
    • Get Help with Power BI
    • Desktop
    • Service
    • Report Server
    • Power Query
    • Mobile Apps
    • Developer
    • DAX Commands and Tips
    • Custom Visuals Development Discussion
    • Health and Life Sciences
    • Power BI Spanish forums
    • Translated Spanish Desktop
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Training and Consulting
    • Instructor Led Training
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Events
    • Ideas
    • Custom Visuals Ideas
    • Issues
    • Issues
    • Events
    • Upcoming Events
    • Community Engagement
    • T-Shirt Design Challenge 2023
    • Community Blog
    • Power BI Community Blog
    • Custom Visuals Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 

    Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

    • Power BI forums
    • Galleries
    • Quick Measures Gallery
    • To *Bleep* with RANKX!

    To *Bleep* with RANKX!

    04-22-2020 14:14 PM - last edited 04-23-2020 22:48 PM

    Super User Greg_Deckler
    Super User
    6378 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    To *Bleep* with RANKX!

    ‎04-22-2020 02:14 PM

    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


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Preview file
    250 KB
    ToHellWithRankX.pbix
    Labels:
    • Labels:
    • Other
    Message 1 of 4
    6,378 Views
    8
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    Kniggit270
    Kniggit270
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎04-27-2023 01:39 AM

    @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

    Message 4 of 4
    1,418 Views
    0
    Reply
    glissando
    glissando Resolver II
    Resolver II
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎04-26-2020 06:16 PM

    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

     

    Message 2 of 4
    6,138 Views
    0
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to glissando
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-04-2020 09:33 PM
    Depends on the parameters you provide to RANKX but definitely solvable.

    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    Message 3 of 4
    6,001 Views
    0
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Partners
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2023 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks
    Consumer Privacy Act (CCPA) Opt-Out Icon Your Privacy Choices