Sorting a column by using another column is one of the nice features of the new version of PowerPivot introduced with SQL Server 2012 (we call it PowerPivot v2, but it really v11 because it is aligned with SQL Server). For example, this allows you to sort the Month name in the right order (January, February, …) instead than in alphabetical order. Usually, you should have a month number column that allows you to correctly sort the month name. However, sometime it is necessary to build a calculated column in order to provide the right sort, as you will see in this article.
UPDATE 2017-06-07: the Windows Azure Marketplace is no longer available, so certain links included in this article no longer work.
Sorting Dates Columns in PowerPivot
First of all, we will use a Calendar table as an example, also because it is a very common scenario in which you will want to use this feature. In order to create a Calendar table you can use the DateStream data source on Windows Azure Marketplace. In order to get just a range of years, you have to manually change the URL by using the URL/OData syntax described by Boyan Penev in his blog.
Now, in order to sort the MonthName, you click the Sort by Column button in the Home ribbon of the PowerPivot window after you selected the column to sort. For example, in the following picture you can see how to sort the MonthName column by the MonthOfYear value.
At this point, when you will browse your data with a PivotTable or Power View you will see the months sorted in the proper order. However, you can discover that it is not so easy to set the proper sort for the MonthInCalendar column, which contains 12 months for each year and has to be sorted by Year and Month Number.
The problem is that we do not have a corresponding column to use for the sorting. We might try to use:
- DateKey that has a different value for each day. Thus, different rows for the same month and year have different values for DateKey and you get the error “Cannot sort MonthInCalendar by DateKey because at least one value in MonthInCalendar has multiple distinct values in DateKey. For example, you can sort [City] by [Region] because there is only one region for each city, but you cannot sort [Region] by [City] because there are multiple cities for each region”, as you can see in the following screenshot.
- MonthOfYear that has a total of 12 numbers corresponding to the months, but does not have the year. You do not get an error, but as a result you would see January month for all the years in your calendar, then all February months and so on, as you can see in the following picture.
- YearKey that has a value for each year, but toes not have the month and produces as a result that months are sorted in alphabetical order within each year
In order to solve this issue, we need a column that has the same granularity as the MonthInCalendar column (year and month). We can create a calculated column YearMonth with the formula [YearKey] * 100 + [MonthOfYear], as you can see in the following picture:
The same technique can be applied to other composite columns such as, for instance, QuarterInCalendar. These columns are important in case you want to display, for example, a chart where data are plotted month by month. Using a column such as MonthInCalendar is also useful when you define a hierarchy Year-Month-Day. In fact, you have to remember that in a report it could be not obvious what is the year to which a month belongs to, and having a column containing both month name and year could be fundamental in order to make the report easy to read.
Sorting Dates Columns in BISM Tabular
The Sort by Column problem that you have seen in PowerPivot is identical in BISM Tabular. The only difference is that the user interface for specifying the Sort by Column of an attribute is included in the Properties pane, but you can also use the same Sort by Column dialog box that you have seen in PowerPivot by selecting the Column / Sort / Sort by Column… menu item after you selected the column of which you want to control the sort order.
In this article you have seen how to control the sort order of a column in PowerPivot and BISM Tabular. The lesson learned is that you may have to create a calculated column in order to provide the sort order information with the same granularity of the column that you want to sort in a custom way.