Forum Discussion
Sorting row or column values in a specific order
Hi, this is a 2-part question.
Part #1:
I followed a previously published solution: https://community.powerbi.com/t5/Desktop/Sorting-rows-and-columns-in-matrix-visual/m-p/723838#M349347
to try to sort a field in a matrix row. The field name is "LOB" and the sort column field I created is "Sort_LOB" where:
Part #2:
In the same matrix, I also display a column field with 2 values - Coaching and Pre-Coaching.
As you can see from the screenshot -
the 2 values are displayed in alphabetical order, "C" then "P".
But I really want it to display Pre-Coaching, then Coaching.
How do I do that? Do I also create a new field with sort order specified like in the DAX for LOB, which that one didn't seem to work in the visualization!
Thanks.
10 Replies
Part #1:
Is the "Sort_LOB" field a calculated column? If it is, you should use sort by column in the Data View
You select the "LOB" column and you sort by the Sort_LOB column.
Instead of using IF, its better to use SWITCH.
Sort_LOB = SWITCH( [LOB] , "Accounts", 1 , "ESC Accounts", 2 , "Billing" , 3 , "ESC Billing" , 4 , "Tech" , 5 , "ESC Tech & Hardware" , 6 , 7 ) --- 7 is for any other valuesPart 2:
Here is a post explaining how to sort by custom orders.
http://www.excelnaccess.com/custom-sorting-in-power-bi/You could use the same method as in the row order.
I hope this helps.
is [Lob] a measure or a Column?
You could also create a new table (Sort_Lob) to do the sorting.
LOB Sort_LOB Accounts 1 Esc Accounts 2 Billing 3 Esc billing 4 Tech 5 Esc Tech & hardware 6 Then you relate this table to the Employee_Team table you want to sort, and use this Sort_Lob table in the report.
hi Anonymous
You need to add two conditional column in edit queries as below:
Then use sort by column in power bi desktop the row and column.
https://radacad.com/sort-by-column-in-power-bi
Result:
and here is my sample pbix file, please try it.
Regards,
Lin
