topic Re: LARGE in Quick Measures Gallery

LARGE

Greg_Deckler — Wed, 06 May 2020 14:13:29 GMT

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

Re: LARGE

Anonymous — Tue, 21 Feb 2023 12:59:52 GMT

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?

Re: LARGE

Greg_Deckler — Tue, 21 Feb 2023 15:18:31 GMT

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

Re: LARGE

Anonymous — Tue, 21 Feb 2023 22:38:18 GMT

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