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