Forum Discussion

AlB's avatar
AlB
Community Champion
7 years ago
Solved

How to sort TOPN results

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...
Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    7 years ago

    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.

affan's avatar
affan
Solution Sage
7 years ago

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's avatar
AlB
Community Champion
7 years ago

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's avatar
    v-chuncz-msft
    Community Support
    7 years ago

    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.