Forum Discussion

deepak_d03's avatar
deepak_d03
Helper I
1 year ago
Solved

DAX query - Sorted incorrectly

Data set (Table name - Table1) has the insurance complaints inforamtion. I am trying to get the top 10 days where maximum complaints have been filed. 

 

Below is the DAX query result. Its not sorted correctly. Can anyone tell what going wrong?

 

TOPN(
        10,
        SUMMARIZECOLUMNS(
            Table1[Received date],
            "Complaint Count", COUNTROWS(Table1)
        ),
        [Complaint Count],
        DESC
    )
 
  • AlexisOlson's avatar
    AlexisOlson
    1 year ago

    TOPN does not guarantee any sort order for the results.

     

    If you're writing a DAX query, you can add and ORDER BY clause at the end. For example, 

     

    EVALUATE
TOPN (
    10,
    SUMMARIZECOLUMNS (
        Table1[Received date],
        "Complaint Count", COUNTROWS ( Table1 )
    ),
    [Complaint Count], DESC
)
ORDER BY [Complaint Count] DESC

     

3 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    1 year ago

    TOPN does not guarantee any sort order for the results.

     

    If you're writing a DAX query, you can add and ORDER BY clause at the end. For example, 

     

    EVALUATE
TOPN (
    10,
    SUMMARIZECOLUMNS (
        Table1[Received date],
        "Complaint Count", COUNTROWS ( Table1 )
    ),
    [Complaint Count], DESC
)
ORDER BY [Complaint Count] DESC

     

    • deepak_d03's avatar
      deepak_d03
      Helper I
      1 year ago

      That worked. Thanks. 

      If I need to show this in report view,  how do I do that? 

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        1 year ago

        Then you probably don't need any DAX. Just put the columns in a table visual, add a TopN filter in the filter pane, and sort by whichever one you want.