Using DAX to return the Nth highest or lowest value

Hello I have posted this on Mr Excel, but as yet have not had a successful answer;

I want to get the Nth largest of smallest value using DAX , so in EXCEL you'd use LARGE or SMALL.

HTML Code: 

the suggestion I had was to use TOPN and MIN or MAX, so to return the 3rd hight value

TOPN(3 , Table, Table[ Column Numbers ] )

to return a table containing the top 3 values. then wrap in MIN to get the lowest , 3rd.
I've tried variuos options none of which has worked,

  MIN(    TOPN(3 ,  Table, Table[  Column Numbers ] )  )

MIN(    TOPN(3 ,  Table, Table[  Column Numbers ] ) , Table[Numbers]  )

etc.

Does anyone have any suggestions?

Richard.

Hi,

Perhaps attach a workbook with a small example together with your unsuccessful attempts.

Regards

For 3rd largest:

With TOPN:

PHP Code: 

MINX(
    TOPN( 3, SUMMARIZE( Table1, Table1[Units] ), Table1[Units], DESC ),
    [Units]
) 


With RANKX:

PHP Code: 

VAR MyTable =
    SUMMARIZE(
        Table1,
        Table1[Units],
        "Rank", RANKX( ALL( Table1 ), Table1[Units] )
    )
RETURN
    MAXX( MyTable, IF( [Rank] = 3, [Units] ) ) 


Regards

Hi,
Haven't actually got that to work, but I'll persevere, the table returned to MIN is the Summarized table,
but I still get an error?

I have come up with one long winded way, involving 'helper table'
Select a cell along table1
DATA , Existing connections, select Table1, or whatever, past along side , right click; Table ; Edit DAX
EVALUATE
TOPN(3, Table1,Table1[Units])

Enter and a table of top 3 items is returned and then a straight forward min measure ,
I've attached example.


RD

MIN has to refer to an actual physical column within an existing table, so you can't pass TOPN to it (which returns a virtual table).

Regards

Originally Posted by Dicken

Really sorry, I mistakenly deducted reputation, meant to click approve, if I can reverse this I will.

Richard

I have corrected - and reversed - the neg rep you gave.

You've got too many arguments there. It would just be:

=MINX(TOPN(3,Table1,Table1[Units]), Table1[Units])

Thanks a lot, Ford!

No problem