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

[Anonymous]

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

[Anonymous]

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]

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]

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

[Anonymous · Accepted solution]

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?