LARGE

Question

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])
&nbsp;
eyJrIjoiOGI3ZGE2NjUtZTNmYi00ZmFhLWEwYzAtOWNhZWU2NjVmZmU2IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

greg_deckler · Answer

Anonymous&nbsp;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

anonymous · Answer

Hi&nbsp;Greg_Deckler&nbsp;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.&nbsp;&nbsp;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?&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;Greg_Deckler&nbsp;Thanks! This works with a couple of modifications for the purpose I need, so it can be listed in a table.&nbsp;&nbsp;See below for anyone interested.&nbsp;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&nbsp;