Sort By Column (non-unique values)

[Richard_WQE]

I work in 6th Form College and we're just getting started with Power BI to analyse student progress throughout the year.

I've got a simple matrix that I want to sort differently.

For GCSEs this should go 9 down to 1 then U then X then Invalid.  I've set up a numeric "grade rank" for each grade at each subject level but because values like "U", "X" and "Invalid" aren't unique to any subject level (e.g. GCSEs, A-levels, BTECs could all get "U" grades), Power BI doesn't let me use my grade rank to sort the grade using Sort By Column.

I've tried using grade rank as a lookup field in the data set and as a linked table.  Neither way lets me sort within the data set and as far as I can tell, I can only sort the matrix visual by fields I put in the matrix (I tried to put grade rank in and then set it to hide in report, but that didn't seem to do anything).

Any ideas?

Version: 2.115.663.0 64-bit (March 2023)

[joaoribeiro]

Hi @Richard_WQE ,

Can you please share how you created the numeric grade rank?

Thanks!

[Richard_WQE]

The data is from a view so I created a table on the server listing all possible grade-subject level combinations and manually ranked them best-to-worst.  I linked the table to to view.

I've also tried importing the table as its own data set and linking within Power BI Desktop.  I can look the correct values up this way, but I can't sort on them.  😞

[joaoribeiro]

Hi @Richard_WQE , thank you for your explanation.

In this new table, were you able to "sort by column" based on your custom rank? If so, in order to use this new column to sort the grades in the wanted way, I suggest you creating a relationship between the 'sort table' and the 'original table', and then use the column from the 'sort table' instead of the original one in the visuals (matrix in this case).

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️or giving it a kudoe 👍

Thanks!
Best regards,
Joao Ribeiro

[Richard_WQE]

I cannot.  Same error as when I try within the main table.

The grade (in this case the Minimum Expected Grade) isn't unique because, as before, different subject levels can have same grade, so grade isn't unique.

[joaoribeiro]

I see! Unfortunately, there is no workaround for the sort by column without unique values. In this case, can't you create the sorting rank based on the combination of two columns (subject-level)?

[Richard_WQE]

I did try that, but as the new subject-level/grade field isn't in the matrix I can't sort by it.