Forum Discussion

Richard_WQE's avatar
Richard_WQE
New Member
3 years ago

Sort By Column (non-unique values)

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)

Need Help

6 Replies

    • Richard_WQE's avatar
      Richard_WQE
      New Member
      3 years ago

      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's avatar
        joaoribeiro
        Impactful Individual
        3 years ago

        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