Overview
Column sorting lets you order the rows of a table by any column, directly in the table. You can sort List Tables and drill-through tables by clicking a column header.
Storyboard Designers can also save a default sort on a Storyboard List Table. The table then opens in that order for everyone who views the storyboard.
Sorting a table by column
Click a column header to sort the table by that column. The sorted column shows a sort icon in its header.
Each click moves to the next step in this cycle:
- First click: sorts ascending (↑), from A to Z or smallest to largest.
- Second click: sorts descending (↓), from Z to A or largest to smallest.
- Third click: clears the sort, and the table returns to query order.
Clicking a different column sorts by that column instead. To keep the current sort and add another column, see Sorting by more than one column.
If the table has a default sort, the default column already counts as sorted. Clicking it carries on from its current direction. For example, if the default sort is descending, one click clears the sort and the table returns to query order, not to the default sort.
Sorting by more than one column
To add another column to the sort, hold Shift and click its header. The table is sorted by the first column, then by the next column within matching values. For example, sort by Gender, then Shift+click Average Annual Salary to order salaries from highest to lowest within each gender.
Each sorted column shows a sort icon. The icons don’t show which column sorts first, so the order is the order you clicked them in.
Sorting by more than one column is temporary. It isn’t saved with the storyboard, and other users don’t see it. It’s kept and reset in the same way as any sort you apply by clicking a header. See When your sort is kept or reset. A default sort can use one column only.
Blank values
Blank values always sort last, in both ascending and descending order. This applies whether you sort by clicking a header or use a default sort. A value of zero is not blank and sorts with the other numbers.
When your sort is kept or reset
A sort you apply by clicking a header is temporary. It is not saved, and other users don't see it. It stays in place while you keep working on the storyboard, and resets when the table reloads or the tile changes.
| Your sort is kept when you | Your sort resets when you |
| Change page filters | Refresh the page |
| Move between pages of a paginated table | Switch storyboard tabs |
| Enter or leave Modify mode | Change the tile in the query designer |
| Open the AI assistant or a drill-through | Show or hide a column on the tile |
When your sort resets, the table returns to its default sort if one is set. If not, it returns to query order.
Setting a default sort on a Storyboard List Table
A default sort saves one column and one direction on a List Table tile. Everyone who opens the storyboard sees the table in that order.
To set a default sort
- Open the storyboard in Modify mode and edit the List Table tile.
- Go to the Design tab and expand the Sorting section.
- Turn on Default Sort. The toggle is available once the table preview has loaded.
- Sort By selects the first visible column, and Direction selects Ascending.
- In Sort By, choose the column to sort by. You can type to search the list.
- In Direction, click Ascending or Descending. The preview updates to show the new order.
- Click Update on Storyboard, then save the storyboard.
If you close the tile without clicking Update on Storyboard, your changes are discarded and the previous setting is kept.
Clicking a column header in the preview only sorts it temporarily, as it does for viewers. It doesn't change the Sorting settings.
What viewers see
The table opens sorted by the default column and direction. The sorted column shows an ascending or descending icon in its header. Hover over the icon to see the tooltip Default sort.
Viewers can still sort by any column temporarily. See When your sort is kept or reset.
To remove a default sort
Turn off Default Sort, click Update on Storyboard, then save the storyboard. The table returns to query order.
Sorting by a hidden column
Sort By lists every column in the table, including hidden columns. Hidden columns are shown as Column name (hidden).
You can sort by a hidden column, for example a helper column you don't want to display. The table is sorted by that column, but no sort icon is shown because the column isn't visible.
If the sort column is no longer available
If the default sort column is no longer in the results, the default sort is kept and the table uses query order. For example, this happens when the column is removed in Define, or when a pivot column isn't in a viewer's data.
In the Design tab, Sort By shows Column not available, with a note that gives the column's key. To fix it, choose another column or turn off Default Sort.
Limitations
- You can set a default sort on one column only. Viewers can still sort by clicking a header.
- Default sort is available for Storyboard List Tables only. It isn't available for drill-through tables or charts.
- Default sort isn't available for pivoted tables with Expand / Collapse Rows turned on. See Pivoted tables.
Using sorting with other table features
Pagination
Sorting applies to every row in the table, not only the current page. Rows are ordered across all pages, and the sort is kept when you move between pages.
Data limit
A table shows up to 1,000 rows. When a query returns more than that, the table shows this message:
Data limit reached; results incomplete. Sorting applies to the displayed rows only. Refine or export query for full accurate results.
Sorting, including a default sort, orders only the 1,000 rows that are displayed. It doesn't sort the full result. To sort the full dataset, refine the query so it returns fewer rows, or export the data.
Subtotals and custom totals
Subtotal and custom total rows are sorted along with the other rows:
- When you sort a metric column, the values in summary rows are sorted numerically with the other values in that column.
- When you sort a time dimension column, labels are sorted chronologically. Subtotal and custom total labels are sorted alphabetically with the other labels, which may produce unexpected results.
Table totals
Table totals are not sorted. They always stay at the bottom of the table.
Hidden columns
Hiding a column resets any temporary sort. The table returns to its default sort if one is set, or to query order. Reapply your sort after you change which columns are shown.
A default sort on a hidden column keeps applying. See Sorting by a hidden column.
Pivoted tables
Impact on data structure
Sorting a pivoted table can change how its data is arranged, and may break up hierarchies or grouped rows. Check the result before you rely on a sorted pivoted table.
Expand / Collapse Rows
Default sort isn't available for a pivoted table with Expand / Collapse Rows turned on. Sorting would separate child rows from their parent rows. Instead of the sorting controls, the Sorting section shows this note:
Default sorting isn't available while "Expand / Collapse Rows" is enabled.
If a default sort was saved before you turned on Expand / Collapse Rows, it is kept but not applied. It applies again when you turn off Expand / Collapse Rows.
Viewers can still sort these tables by clicking a column header. The sort applies only to rows that are expanded at that time. Rows expanded later don't follow the sort order, so sort again after you expand more rows.
Mixed data types
In a pivoted table, sorting applies to all metrics in the same way. This may give unexpected results when the table combines different data types, such as percentages and counts. For more control over sorting, use a non-pivoted table.
Comments
0 comments
Please sign in to leave a comment.