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
    • 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
    • Microsoft Power BI Community
    • Welcome to the Community!
    • 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 Community
    • 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: 
    • Microsoft Power BI Community
    • Galleries
    • Quick Measures Gallery
    • LARGE

    LARGE

    05-06-2020 07:13 AM

    Super User Greg_Deckler
    Super User
    2324 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

    LARGE

    ‎05-06-2020 07:13 AM

    In my recent quest to create or catalog as many DAX equivalents for Excel functions, we have now arrived at the double secret RANKX pattern. Nifty.

    LARGE = 
        VAR __k = [k Value]
        VAR __Table =
            ADDCOLUMNS(
                'Table',
                "Rank",RANKX('Table',[Value])
            )
        VAR __RanksTable = DISTINCT(SELECTCOLUMNS(__Table,"Rank",[Rank]))
        VAR __RanksTable1 = 
            ADDCOLUMNS(
                __RanksTable,
                "LargeRank",RANKX(__RanksTable,[Rank],,ASC)
            )
        VAR __Rank = MAXX(FILTER(__RanksTable1,[LargeRank] = __k),[Rank])
    RETURN
        MAXX(FILTER(__Table,[Rank]=__Rank),[Value])

     

    eyJrIjoiOGI3ZGE2NjUtZTNmYi00ZmFhLWEwYzAtOWNhZWU2NjVmZmU2IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9


    @ 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
    24 KB
    LARGE.pbix
    Labels:
    • Labels:
    • Other
    Message 1 of 4
    2,324 Views
    1
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    poulsea
    poulsea
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎02-21-2023 02:38 PM

    Hi @Greg_Deckler 

    Thanks! This works with a couple of modifications for the purpose I need, so it can be listed in a table. 

     

    See below for anyone interested.

     

    LARGE 3 = 
        VAR __k = [k Value]
        VAR __Sorted = CONCATENATEX('Table', [Value], "|",[Value],DESC)
        VAR __Table = 
            ADDCOLUMNS(
                'Table', 
                "__Series" GENERATESERIES(1,COUNTROWS('Table'),1),
                "__Value", PATHITEM(__Sorted,__k)
            )
        VAR __Result = MAXX(FILTER(__Table, __k = __k),[__Value])
    RETURN
        __Result

     

    Message 4 of 4
    754 Views
    0
    Reply
    poulsea
    poulsea
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎02-21-2023 04:59 AM

    Hi @Greg_Deckler 
    This large function that you have build does not really function the same way as the large function i Excel. In Excel it returns the value in the row with the k highest value. 

     

    I have tried to restructure your formula to get it to work in a similar to Excel by inserting an additional step of TopN, however, I can get it to work. - Do you have a solution to get the above to work in a similar way to Excel?

     

    poulsea_0-1676984251517.png

     

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

    ‎02-21-2023 07:18 AM

    @poulsea Here's the fix. Relies on a technique that I hadn't developed quite yet when I posted this. You can't get there with TOPN, RANKX, etc. because of the duplicates.

    LARGE 2 = 
        VAR __k = [k Value]
        VAR __Sorted = CONCATENATEX('Table', [Value], "|",[Value],DESC)
        VAR __Table = 
            ADDCOLUMNS(
                GENERATESERIES(1,COUNTROWS('Table'),1),
                "__Value", PATHITEM(__Sorted,[Value])
            )
        VAR __Result = MAXX(FILTER(__Table, [Value] = [k Value]),[__Value])
    RETURN
        __Result

    @ 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
    765 Views
    1
    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