“I just want to sort by that column there…”
It’s one of those incredibly trivial requirements that end-users just can’t believe is not possible in Power BI (particularly after all the other wizardry you’ve just shown them in your report!) If you try to explain… “but that’s a matrix… you can’t sort a matrix by columns, only tables” they will look at you like you just spat in their tea.
Fear not – IT IS POSSIBLE! You just have to put in a little bit of extra work. Props to our Power BI developer Redmond Hartop for going that extra mile in-terms of UX and cracking it.
Car Sales contains some dummy data:
A matrix view, with ItemType as the row headers and country as the column headers and a sum of price, looks like this:
It is currently not possible to sort the matrix by a country by clicking on the column headers like you can with a table visual.
To do so we need to implement a workaround that starts by creating a table of the values we wish to sort by, in our case Country.
This table can then be linked to the fact table with a non-active relationship.
Next add a single select slicer containing values from the Country table:
Next comes a little DAX trick that we can use:
VAR Sales =
SUM ( ‘Car Sales'[Price] )
VAR SortContext =
DISTINCTCOUNT ( ‘Car Sales'[Country] ) > 1
SUM ( ‘Car Sales'[Price] ),
USERELATIONSHIP ( ‘Car Sales'[Country], ‘Country'[Country] )
The SortContext determines if more than one Country is selected. It seems odd as the matrix only seems to calculate for a single country at a time. Power BI does however run the DAX without the column filter.
In the RETURN we check to see if more than one country is selected, if so, we filter this calculation by activating the inactive relationship between the two tables. This filters our sort by values, and thereby sorting the entire table by our selected column.
If your table does not sort after doing this then check that your measure is selected as the table sort by option in the table settings:
One inevitable down-side of this workaround is that the native totals won’t work (the slicer modifies the total which is what enables the sort in the first place). In-order to display (and sort by) row totals you need to create your own total with DAX which is shown in the sample file.
So now your end-users will no longer ask you why they can’t sort those columns. Instead they will say…
“why can I sort that table by clicking on the column header and this one I have to use a slicer?”