I'm creating a pivot table by connecting to an SSAS tabular model (Data -> From Other Sources -> From Analysis Services) .
The model has a "time" column that I want to sort by. The default (database) sorting is earliest to latest:
When I click the triangle next to 'Row Labels' and select "Sort A to Z", I get alphabetically sorted times:
How can I get the times to sort by time? Changing the number format from "General" to "Time" does nothing. The times aren't stored as text either - the data type of the column in the SSAS model is Auto (Date)
2 Answers
You should change the number format of the Row Field to Custom hh:mm:ss AM/PM from Time.
Row Labels > Field Settings > Number Format > Custom > hh:mm:ss AM/PM
This solved your issue for me.
If you are trying to sort a column in a table by time - you can only enter numeric values for time (8:30 - 8:30AM, 13:30 = 1:30PM). If you include the AM/PM modifier, Excel will view it as a text field even if you default the format to time.
Enter in only numeric values, change the cell number format to "Time" and voila, the sort will be by time.
