Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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?
@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