Forum Discussion
Sort Matrix by Year (descending)
Hi,
I have a matrix that looks like this:
Year 2018 2019
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May
Apples 1 2 3 4 3 1 1 2 3 4 3 2 1 2 3 4 3
Pears 2 1 1 2 2 1 2 2 3 2 2 1 2
My question is, how do I sort it by year (not month) so that 2019 comes before 2018?
The dates are a hierarchy from a CALENDAR function like this:
Hi, robincard
Based on your description, you can create a calculated column as follows.
Rank = RANKX('Table','Table'[Year],,DESC,Dense)
You may go to ‘Modeling’ ribbon, click ‘Sort by Column’, select ‘Rank’ column.
Result:
As a workaround, you can also click ‘Edit Query’, go to Query Editor, choose ‘Add column’ ribbon, select ‘Conditional Column’.
You may configure as follows, then go to ‘File’ ribbon, click ‘Close and Apply’.
Then you can make ‘Year’ column selected, go to ‘Modeling’ ribbon, click ‘Sort by Column’, select ‘Custom’ column. Here is the result.
If I misunderstand your thought, please show me your sample data and expected output. Do mask sensitive data before uploading. I am glad to solve the problem for you.
Best Regards,
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 Replies
Tahreem24Super User
You need to create index column for Date Column and use the Sort by COlumn option to sort your Date according to Index. For better understanding follow the beloe step:
1. Go to Query Editor --> Select your ReportDate Column --> Add Column Menu --> Select Index Column --> Apply and close. SO it will create index column.
2. GO to Data Tab --> Select ReportDate Column --> Modelling Menu --> Sort By COlumn and Select Index.
3. Your matrix data is sorted accordingly.
Don't forget to hit THUMBS UP and mark it as a solution if it helps you!
v-alq-msftCommunity Support
Hi, robincard
Based on your description, you can create a calculated column as follows.
Rank = RANKX('Table','Table'[Year],,DESC,Dense)
You may go to ‘Modeling’ ribbon, click ‘Sort by Column’, select ‘Rank’ column.
Result:
As a workaround, you can also click ‘Edit Query’, go to Query Editor, choose ‘Add column’ ribbon, select ‘Conditional Column’.
You may configure as follows, then go to ‘File’ ribbon, click ‘Close and Apply’.
Then you can make ‘Year’ column selected, go to ‘Modeling’ ribbon, click ‘Sort by Column’, select ‘Custom’ column. Here is the result.
If I misunderstand your thought, please show me your sample data and expected output. Do mask sensitive data before uploading. I am glad to solve the problem for you.
Best Regards,
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thanks for your help v-alq-msft . The Rank column looks good but I cannot use it to sort the ReportDates[Date] column as it is calculated from that column. It says:
"This column can't be sorted by a column that is already sorted, directly or indirectly, by this column."
I can add Rank to the matrix columns which gives the desired result, it's just unfortunate that Rank is displayed on the matrix.
- v-alq-msft
Hi, robincard
When you create the calculated column, you need to put it in the matrix for ranking. Have you tried the workaround? As I said before, you may create a conditional column called 'Custom' in the Query Editor and make 'Year' column sorted by 'Custom' column.Best Regards
Allan
If I misunderstand your thought, please show me your sample data and expected output. I am glad to solve the problem for you.
