Forum Discussion

shamsuddeenvp's avatar
shamsuddeenvp
Icon for Post Patron rankPost Patron
10 years ago

How can i user sort by other column in live/direct query to SQL server table

Dear Team,   How can I sort a visual by a column which is not used in the visual but available in data set. I know we can do this in SSAS cube. I want to implement this for the live/direct query on...
Need Help
Kavita's avatar
Kavita
Frequent Visitor
9 years ago

Well I was also breaking my head on getting this done, specifically with respect to month sorting based on the month number. With the help of my collegue we could find a solution.

On the PBI desktop, the "Fields" blade select the column you want to sort (the column based on which you want to sort should be present in the same table),  under the "Modelling" tab at the ribbon the "Sort by Column" is enabled and you can use any other column for sorting.

 

This works absolutely fine for the Direct Query mode. I have used this with Azure SQL

  • reubster's avatar
    reubster
    Frequent Visitor
    9 years ago

    "This works absolutely fine for the Direct Query mode"

    Err.. not for me it doesn't. The entire Modelling ribbon is disabled. 

    I can sort by a column in the Tabular Model, but not by a measure value.... or can I? 

     

     

    • Kavita's avatar
      Kavita
      Frequent Visitor
      9 years ago

      Not sure why the Modelling ribbon is disabled for you! 

       

      This is how it looks for me in Direct Query mode.

       

      And you cannot sort based on measure :(

      • reubster's avatar
        reubster
        Frequent Visitor
        9 years ago

        Hi,

         

        Apologies for the delay, I finally found this reply. 

        Looks like there's a functionality difference between Direct Query to SSAS, and Direct Query to SQL... and it also depends on the data type.

        Cheers,

        Reuben

         

         

         

  • RyanP's avatar
    RyanP
    Icon for Advocate II rankAdvocate II
    7 years ago

    Wasn't working for me...   

     

    Have a TEXT field with the Month names, and a numeric field for each month's number.

    With the TEXT field as the X-Axis, I then select SortBy Column, selecting the numeric field; but I guess it's not sorting since the Axis is stuck on categorical

     

    Added the numeric field to the Axis, then having both the text and numeric listed (text first). and voila, c'est bon