# Using DAX to return the Nth highest or lowest value

1. ## 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:
``  https://www.mrexcel.com/board/threads/dax-nth-largest-value.1208250/``

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.

2. ## Re: Using DAX to return the Nth highest or lowest value

Hi,

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

Regards

3. ## Re: Using DAX to return the Nth highest or lowest value

I cant really give a full example as TOPN returns a table, not a value,
I've tried attaching a screen shot from studio but can't get it to work. I've attached a workbook with minimum value and I want to return the 3rd , for arguments sake.

MIN(
TOPN( 3, Table1,Table1[Units]) )

4. ## Re: Using DAX to return the Nth highest or lowest value

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

5. ## Re: Using DAX to return the Nth highest or lowest value

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

6. ## Re: Using DAX to return the Nth highest or lowest value

Just to update , it's now working, as far as I can see the main difference is the use of SUMMARIZE and MINX rather than MIN,
how does this make a difference?
RD

7. ## Re: Using DAX to return the Nth highest or lowest value

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

8. ## Re: Using DAX to return the Nth highest or lowest value

Thanks, that is something I'd never considered, I always thought they would be treated the same.

RD

9. ## Re: Using DAX to return the Nth highest or lowest value

Just to add I've put all the different stages into studio, I've only been using it a couple of weeks and don't know how i managed before,

I'm still not sure why SUMMARIZE, can see a single column table returned, as opposed to the entire table

I know this doesn't work but can't see why;

``Please Login or Register  to view this content.``
can't work,

Richard.

10. ## Re: Using DAX to return the Nth highest or lowest value

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

``Please Login or Register  to view this content.``

11. ## Re: Using DAX to return the Nth highest or lowest value

Yes, you are right , once again, when I first started trying to solve this I had too few, can't win.

rd

12. ## Re: Using DAX to return the Nth highest or lowest value

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

Richard

13. ## Re: Using DAX to return the Nth highest or lowest value

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.

14. ## Re: Using DAX to return the Nth highest or lowest value

Thanks a lot, Ford!

No problem

16. ## Re: Using DAX to return the Nth highest or lowest value

Thanks,

rd

There are currently 1 users browsing this thread. (0 members and 1 guests)