Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

How to sort a text month year column by a numeric date column in a live/direct query to SAAS Cube

Hi team

 

Looking for some urgent help on sorting my visual by a month year format as opposed to an alphabetic format.
i am using a direct query to a Saas tabular model and woul like to sort the text mm-yy column on a date key column in my model. In my view i am trying to show a view of created tickets and a cummulative count of Open tickets per month. Any ideas/solutions will be really appreciated. cheers

 

 

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    8 years ago

    Cheers guys for the help, on another note just wondering if there was any ideas around modelling this back within the Saas tabular model so that it flows seamlessly into to powerbi?

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Anonymous

     

    There is no direct option to sort text month year column, try to sort manually by the following way-

    1. Create a calculated column and write the below DAXMonthYear Sort = SWITCH(Table1[Month Year],"Jan-2015",01,"Feb-2015",02,"Mar-2015",03,"Apr-2015",04,"May-2015",05,"Jun-2015",06,"Jul-2015",07,"Aug-2015",08,"Sep-2015",09,"Oct-2015",10,"Nov-2015",11,"Dec-2015",12,.......)
    2. Sort "Month year" column by "MonthYear Sort" Column 

    For more details see the pbix file. Hope this will help you!!!

    Download pbix file

     

    Regards,

    Angad

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Angadk,

       

      Unfortunately there isn't an ability to create  a calculated column when running a Saas direct query to a Tabular model.
      Thanks for trying :)

      • v-cherch-msft's avatar
        v-cherch-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi Anonymous

         

        It seems you may try to sort this in SSAS model itself as live/direct query does not have the ability to perform sorting.

         

        Regards,

        Cherie