Custom sorting stopped working

[MacN]

Hi All, 
I'm having difficulties setting up custom sorting in the new dashboard, which is quite bizarre as a solution that has worked before now does not.

I'm attaching .pbix file where I try to set up two solutions, but both are not working. 
https://drive.google.com/file/d/1KLeD8fJdQcx_8MOLlYGACC5fdazY8W_t/view?usp=sharing

Data:

• Dummy actions data with:
  • action_id
  • action_grade with values: High, Moderate, Low, NA, and null
  • days_overdue, which is a random number between 1-1000
  • Days Overdue Grouping based on days_overdue, with values: 1-60, 61-180, 181-365, 366-500, 501+

Desired result: 

• For the Days Overdue Grouping to be sorted from lowest to highest value.
• For the action_grade to be sorted in order: High, Moderate, Low, NA

Solution 1 - Relationship with a Sorted Table:

• On the example of 'Days Overdue Grouping':
  • An index table with values from 'Days Overdue Grouping' and index was created.
  • A relationship between the Index table and the main table was established.
  • In the index table, 'Days Overdue Grouping' is sorted by index.

The sorting is not visible in the chart based on the main table. This method worked successfully before.

Solution 2 - Sort by Merged Column:

• On the example of action_grade:
  • An index table with values from action_grade and index was created.
  • The 'Action Grading Index' table was merged into the main table.
  • In the main table, I try to sort action_grade using the 'Action Grading Index'.

I encounter this error: "We can't sort the 'action_grade' column by 'Action Grading Index.1'. There can't be more than one value in 'Action Grading Index.1' for the same value in 'action_grade'..."

Which is confusing as 'Action Grading Index' has unique values for values of action_grade.

I get the same error, when I create 'Action Grading Index' via the custom column and not by merge.  

Please help to find a solution to custom sorting that works. 

In advance, thank you for any solutions or workarounds. 

[MacN]

Hi,  
Thank you a lot for a quick reply.  
Your suggestion has helped. It looks like I missed the step where I should replace 'Days Overdue Grouping' from the main table with values coming from the related table.  
I'm pleasantly surprised that this also works for the dashboard I'm working on, where dimensions for the charts are coming from Fields Parameters. I just replaced references in the parameter from the main table to the index tables. 

Just out of curiosity; do you know why the second solution isn't working? 
  

[sumanth_v · Accepted solution]

You can create a new table with Enter Data option from Home ribbon, Enter your Days overdue grouping values in one column and enter valeus 1,2,3... for second column and name it as index and sort the table based on index column.

In your visual, add the days overdue filed from this new table and remove the old one. Sort your visual based on the days overdue column and it will be sorted in the way you want.

Let me know if you need more assistance.