Forum Discussion
Helper VAdvice on best method to sort - tried many things!
Hello. I would love some advice on the best method to custom sort dynamic data. I have a table of students' outcomes on various tests. These outcomes are categorical and change all the time. New tests are added to the data set frequently, as are new outcomes for those tests that already exist. I have tried a SUMMARIZE table, I have tried a reference table, and other methods that I am forgetting at the moment. In any case, I have been unsuccessful. Here is a portion of dummy data from the bigger, complete table of data. The "How I would like to sort" column is what I am attempting to accomplish. My end goal is to have bar graphs/matrices that display the following outcomes in the preferred order shown in the table below. Thank you for your input!
| Test | Outcome | How I would like to sort |
| Test 1 | Proficient | 1 |
| Test 1 | Exemplary | 2 |
| Test 1 | Developing | 3 |
| Test 1 | Not Provided | 4 |
| Test 2 | Above Proficient | 1 |
| Test 2 | Proficient | 2 |
| Test 2 | Below Proficient | 3 |
| Test 2 | Not Addressed | 4 |
| Test 3 | Excellent | 1 |
| Test 3 | Satisfactory | 2 |
| Test 3 | Needs improvement | 3 |
| Test 4 | Excellent | 1 |
| Test 4 | Satisfactory | 2 |
| Test 4 | Inadequate | 3 |
| Test 5 | Above Proficient | 1 |
| Test 5 | Proficient | 2 |
| Test 5 | Acceptable | 3 |
| Test 5 | Unacceptable | 4 |
| Test 6 | Exceeds Expectations | 1 |
| Test 6 | Meets Expectations | 2 |
| Test 6 | Below Expectations | 3 |
| Test 7 | Exemplary | 1 |
| Test 7 | Acceptable | 2 |
| Test 7 | Needs Improvement | 3 |
| Test 7 | Unacceptable | 4 |
Hi afaherty ,
First of all I believe this is problem that best solution is in Power Query and not in DAX.
When you refer that you want to sort the values is the ones on the column of the outcome correct, and not add a new column with the ranking is that it?
If that is the situation what you need is to add a sorting order to your data with that sorting number that would allow you to get that exact same sort every time.
There are 15 unique values on that list so you need to setup the sorting order for those specific items them do a sort by that column.
If there was all possible result in a test what would be the order of this items, be aware that the sort order I place is not the correct one please order then has you need:
Outcome Sort Order Proficient 1 Exemplary 2 Developing 3 Not Provided 4 Above Proficient 5 Below Proficient 6 Not Addressed 7 Excellent 8 Satisfactory 9 Needs improvement 10 Inadequate 11 Acceptable 12 Unacceptable 13 Exceeds Expectations 14 Meets Expectations 15 Below Expectations 16 Don't know how you model is setup but best practice is to add this as a dimension table and related with your results and the in this table use the sort by other column option:
If you don't have the start schema you can do a merge of this table with the results and then expand only the sort and the sort by other column is the same has in the image above.
6 Replies
- MFelix
Super User
Hi afaherty ,
First of all I believe this is problem that best solution is in Power Query and not in DAX.
When you refer that you want to sort the values is the ones on the column of the outcome correct, and not add a new column with the ranking is that it?
If that is the situation what you need is to add a sorting order to your data with that sorting number that would allow you to get that exact same sort every time.
There are 15 unique values on that list so you need to setup the sorting order for those specific items them do a sort by that column.
If there was all possible result in a test what would be the order of this items, be aware that the sort order I place is not the correct one please order then has you need:
Outcome Sort Order Proficient 1 Exemplary 2 Developing 3 Not Provided 4 Above Proficient 5 Below Proficient 6 Not Addressed 7 Excellent 8 Satisfactory 9 Needs improvement 10 Inadequate 11 Acceptable 12 Unacceptable 13 Exceeds Expectations 14 Meets Expectations 15 Below Expectations 16 Don't know how you model is setup but best practice is to add this as a dimension table and related with your results and the in this table use the sort by other column option:
If you don't have the start schema you can do a merge of this table with the results and then expand only the sort and the sort by other column is the same has in the image above.
- grazitti_sapna
Hi afaherty,
As already mentioned by MFelix, you need to create a separate order table in power Query and then you'll also have to create the sort order for test cases as well in the main table, I've created a complete solution for you and attached the .pbix file here.
Below are the implementation steps
1. Create a separate sort_order table (Unrelated) with outcome and order columns .
2. Then create order column in data table using lookupvalue.
3. Then adding a new column SortOrder so it should show me nos. as per the test group.
4. Then combine test number and SortOrder columns to create FinalSort column.
Do let me know if you need any further assistance.
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUserand Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together! 
v-saisrao-msftCommunity Support
Hi afaherty,
Have you had a chance to review the solution shared by MFelix maruthisp grazitti_sapna? If the issue persists, feel free to reply so we can help further.
Thank you.
- Shai_Karmani
The issue you are hitting is that Power BI model-level Sort by column requires each value in the sorted column to map to exactly one value in the sort column. Your Outcome column fails that rule because "Proficient" is sort order 1 under Test 1 but sort order 2 under Test 2, and "Excellent" is sort order 1 in both Test 3 and Test 4. The engine rejects the setting.
Two clean approaches depending on what the visuals look like.
1. If every chart or matrix is filtered to a single Test (through a slicer, page filter, or small multiples), use visual-level sort instead of model-level. On the visual, open the More options menu (three dots at the top right of the visual), pick Sort axis, then Sort by, then your "How I would like to sort" column. The sort is per-visual, so the duplicate-across-tests problem no longer applies.
2. If you need one visual to show multiple tests at once, build a composite column so the axis is unique:
TestOutcome = 'YourTable'[Test] & " - " & 'YourTable'[Outcome]
SortKey = ( VALUE ( RIGHT ( 'YourTable'[Test], 1 ) ) * 100 ) + 'YourTable'[How I would like to sort]Then set TestOutcome's Sort by column to SortKey. Use TestOutcome as the axis instead of Outcome. The chart orders Test 1 Proficient, Test 1 Exemplary, Test 1 Developing, Test 1 Not Provided, Test 2 Above Proficient, and so on in the exact order you listed.
If your Test labels are not "Test N" style strings, drop VALUE(RIGHT(...)) and add a small lookup table with Test and TestOrder, then join and use TestOrder in the SortKey. Same idea.
If this helped, a thumbs up and marking it as the accepted solution would be appreciated.
Best,
Shai Karmani - v-saisrao-msft
Hi afaherty,
Checking in to see if your issue has been resolved. let us know if you still need any assistance.
Thank you.
