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,

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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

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

Please Login or Register  to view this content.

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.

Thanks a lot, Ford!

No problem