Forum Discussion
How to sort multiple columns separately
Hi,
I would like to explore following scenario:
I have monthly progress variance for 100 projects. For filtered year (e.g. selected year 2023) from Jan to Dec, sort descending by larger delay variance at the tops for each month in a table or matrix then show the project names accordingly.
Is it able to do so?
Hi, AlwaysLearn You can sort multiple columns in table using "Shift + Left clicking" the column header. Left click again for opposite direction. You can then convert the table to matrix if you want.
If this solved your problem, please mark it as a solution!!
Hi AlwaysLearn
Please try this:
Here I create a set of sample:
Then add a calculated column:
Column = ROWNUMBER(ORDERBY( 'Table'[Month], ASC, 'Table'[Values], DESC ))Then you can sort the matrix or table visual by this column, the result is as follow:
ROWNUMBER function (DAX) - DAX | Microsoft Learn
ORDERBY function (DAX) - DAX | Microsoft Learn
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi AlwaysLearn
Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
Hi, AlwaysLearn You can sort multiple columns in table using "Shift + Left clicking" the column header. Left click again for opposite direction. You can then convert the table to matrix if you want.
If this solved your problem, please mark it as a solution!!
Hi shafiz_p
Am I right to say, I need to adjust at data modelling level, instead of looking at report/chart level?
Hi AlwaysLearn
Please try this:
Here I create a set of sample:
Then add a calculated column:
Column = ROWNUMBER(ORDERBY( 'Table'[Month], ASC, 'Table'[Values], DESC ))Then you can sort the matrix or table visual by this column, the result is as follow:
ROWNUMBER function (DAX) - DAX | Microsoft Learn
ORDERBY function (DAX) - DAX | Microsoft Learn
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Step 1: In your Matrix, add a field to the row hierarchy (which you will later remove).
Step 2: Change the Matrix to a Table visual.
Step 3: Sort all the columns you wish to sort, and in the order you wish them to be sorted.
Step 4: Delete the field you previously added in Step 1 from the table.
Step 5: Change the Table back to a Matrix visual.
Step 6: Ensure the right fields are back in the Row hierarchy.
This will maintain your multiple sorted fields within your matrix, even when you expand your row hierarchies.
