Sort by another column error

[cath1ynn]

I've ran into this annoying Sort by another column error that I can't work out how to resolve because there are no duplicates!

I am trying to sort the month name (text) by the FinancialMonth field (whole number) and I keep getting this 

We can't sort the 'responseDateTZ_Month' column by 'ResponseDateTZ_FinancialMonth'. There can't be more than one value in 'ResponseDateTZ_FinancialMonth' for the same value in 'ResponseDateTZ_Month'. Please choose a different column for sorting or update the data in 'ResponseDateTZ_FInancialMonth'.

However, as you can see in the next screenshot, there are no duplicate values for ResponseDateTZ_Month to ResponseDateTZ_FinancialMonth nor the reverse.

I'm not sure what else to check.

I should mention that these fields are in the same table.

[Anonymous]

Hi  @cath1ynn,

This issue has been submitted internally.

Please be patient to wait for fixing. If there is any news, I will update it here.

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cath1ynn]

Hi @Anonymous 

Have there been any update on this issue?

[Anonymous]

Hi @cath1ynn ，
The fix is in the main program and we are waiting for it to roll out.

[Anonymous]

Do you have a link to the issue and/or the issue id-number so I can follow the status?

[cath1ynn]

Ok, I have the same error happening to another datasource in the same report and this time, I was able to export some sample data to CSV and import it into another Power BI report and was able to recreate the error in this sample Power BI report.

How do I upload the sample files here?

[cath1ynn]

I think I have worked out when this error occurs. With my initial dataset where I was having problems with the ResponseDate field, the reason I stopped having problem with the field after refreshing the dataset was because after refreshing, there were no longer records that are missing ResponseDate values. Whereas in this second dataset, there are many records with missing ClosedDate values because they haven't been closed out yet. I realised this when I attempted the Sort by column on the CreatedDate Month field and realised that I didn't get this error, because every record would have a CreatedDate.

I tested this theory by creating a copy of the sample dataset containing only the Id and the relevant ClosedDate fields, then filtering this dataset to only contain records that have a ClosedDate value. I've discovered that I am now able to sort the Month field by the FinancialMonth. If I then go back into Power Query and removed the step where I filtered the data on the ClosedDate field, the Sort by column on the Month field is still working correctly.

However, when I attempted this workaround in the original sample dataset by applying a non blank filter on the ClosedDate field in Power Query then trying to apply the Sort by column on the Month field in Desktop, I get the same error again 😢

I'm out of ideas again...

Here's the link to the sample PBIX file

[foodd]

Hello @cath1ynn , and congradulations on self solving your issue. 

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.  Proud to be a Super User!

[cath1ynn]

You would not believe this but after refreshing the data in preparation to export it to CSV to build another PBIX file to upload, it seems to have fixed itself and now I am able to sort the Month field by the FinancialMonth field 🤔 I made no change to the data model and this wasn't even the first time that I have refreshed the data either. I was butting my head against the wall all of yesterday's afternoon with this error and now it just fixed itself 😠

[BronSuchecki]

Had the same problem, but my sort by column was a DAX lookupvalue. The lookup table had duplicates, I fixed that but then even with no duplicates it would not accept it. Fixed it by just deleting the column and re-entering it with exactly the same DAX code. It is like PowerBI will not clear the error status of the sort by column.

[ryan_mayu]

could you pls proivde the pbix file?