COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
The March 2020 update to Power BI desktop is a full one, as this blog shows!
Part five of a eleven-part series of blogs

New goodies in this month's update include the roll-out (sort of) of the new ribbon, new actions for buttons, multi-column sorting and a second axis for line charts.

  1. The March 2020 Power BI Desktop Update
  2. The new ribbon is live (sort of)
  3. Page navigation buttons
  4. Drill-through navigation buttons
  5. Multi-column sorting (this blog)
  6. Searching in the filter pane
  7. Line charts can now have a dual axis
  8. The new DAX COALESCE function
  9. Changes to ArcGIS
  10. Query Diagnostics
  11. Generating embed codes is now turned off by default

For a cumulative list of all of the updates to Power BI Desktop in the last few years see this blog, or have a look at the Power BI courses that we run.

Posted by Andy Brown on 14 March 2020

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Multi-column sorting

So the problem is simple enough to explain.  I want to sort the following visual by year and then by quantity:

Sorting by two columns

I want to sort sales into year order. Where two or more sales occur in the same year, I want to sub-sort them by quantity.

 

Sorting by two columns

The (new) answer is to hold the SHIFT key down when you click on a column header.  Here's how this could work, using the above table as an example.  Firstly, click normally on the Year column:

Sorting by one column

The data is now sorted by year.

 

Now hold down the SHIFT key and click on the Quantity column:

Sorting by second column

Now I can see the data sorted into ascending year order, and within year into descending quantity order.

 

Hold down the SHIFT key and click on the Quantity column again to change the sort order:

Second column change

Eureka - this is what I wanted (the data is sorted into ascending year order and ascending quantity order).

 

Sorting by more than 2 columns

You can hold down the SHIFT key and click on a third column (and presumably a fourth and a fifth ...):

Sorting by 3 columns

Here I'm sorting by environment, year and quantity.

 

Reversing the priority of columns

To swap the order of the two columns, hold down the SHIFT key and click on the first sort column (the Year).  This changes the table so that it sorts firstly by quantity and then by year.

Click on year column

Hold down the SHIFT key and click on the Year column, which is being used as the primary sort key.

 

Here's what this would then show:

Reversed sort

The data is now sorted by Quantity and only then by Year (although because the quantities are all unique you'll have to take my word for this).

 

All very simple and elegant! 

This blog has 0 threads Add post