LARGE 05-06-2020 07:13 AM

[Greg_Deckler]

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

[Anonymous]

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

[Anonymous]

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?

[Greg_Deckler]

@Anonymous 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