LARGE

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...

LARGE.pbix56 KB

Other

Anonymous

Not applicable

3 years ago

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

Community Champion

3 years ago

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