Reply
Super User
Super User
Posts: 1,274
Registered: ‎11-12-2018
Accepted Solution

How to sort TOPN results

[ Edited ]

Hi there,

The definition of TOPN explicitly states that the function does not guarantee any sort order in its result. Imagine we have a very simple one-column table Table1:

 

Value

1
2
3
4
5
6
7

 

Now we create another one-column table Table2 as follows:

 

Table2 = TOPN(5;Table1;Table1[Value];ASC)

The result for Table2:

 

Value

5
4
2
1

3

 

We see that the result in Table2 is indeed not sorted. Is there any way to sort it? It'd be easy in tools like DAX Studio but how can it be done in Power BI? 

 

Many thanks


Accepted Solutions
Highlighted
Community Support Team
Posts: 4,198
Registered: ‎07-09-2016

Re: How to sort TOPN results

@AlB,

 

As far as I know, there is no reliable way. You could click the filter drop down arrow on the column and select Sort.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post


All Replies
Established Member
Posts: 203
Registered: ‎07-12-2017

Re: How to sort TOPN results

Hi @AlB,

 

Check the attached PBIX file. I am getting the table 2 sorted by default.

 

 

sort table.png

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

 

Regards,

Affan

 

 

 

Super User
Super User
Posts: 1,274
Registered: ‎11-12-2018

Re: How to sort TOPN results

[ Edited ]

Hi @affan

Thanks for your reply. It is sorted in the visual on your report, because the visual sorts it, but what I am talking about is the table itself, that you see in the Data view. That is what would be generated if I used the Table2 code somewhere else. For instance, imagine I want to get the top value from Table2, we could think of doing:

 

FIRSTNONBLANK ( TOPN ( 5; Table1; Table1[Value]; ASC ); 1 )

 

but for that to work properly I need to know the table returned by TOPN is actually sorted with the 1 on the first row. It will not necessarily be so, given how TOPN works. So how can we sort it? I know you could approach the above in a different manner but I am interested in the sorting issue.

 

Thanks       

Highlighted
Community Support Team
Posts: 4,198
Registered: ‎07-09-2016

Re: How to sort TOPN results

@AlB,

 

As far as I know, there is no reliable way. You could click the filter drop down arrow on the column and select Sort.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.