How to sort TOPN results

[AlB]

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

[affan]

Hi @AlB,

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

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

Regards,

Affan

[AlB]

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       

[v-chuncz-msft · Accepted solution]

@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.