Using Direct Connect to SSAS Multidimensional cube and Sorting Data fields in calendar order

[inayatkhan]

A Multidimensional SSAS cube has direct connection to Power BI in the Desktop. The Date fields such as Month ( e.g March 2018) are not sorting ( as defined  and sorted correctly in the cube when browsed directly with Excel)  instead they are being ordered alphanumerical.

The option of adding a sorting column is not applicable, as this is a direct connection, so the Modelling tab is greyed out.

How can this be resolved ? Thanks

[v-chuncz-msft]

@inayatkhan,

You may take a look at https://docs.microsoft.com/en-us/sql/analysis-services/lesson-4-5-sorting-attribute-members-based-on....

[inayatkhan · Accepted solution]

Thanks yes this article is helpful and sorts the members of user defined date hierarchies, however it doesn't work for the Attribute hierarchies. What is needed is a sorted member attribute for month e.g "Month_Sorted" : 201801 , 201802 etc . This is the has a source to target cardinality of the month name to Month_Sorted ,so that it is then available as the Attribute to sort on for the Month_Name attribute.