Forum Discussion
Error when using a column to sort another column
I have a list of values in a filter that need to be sorted. I created a sort table:
I joined it to my table:
I created a new column in my data table "Location_SortB = related('Alt Loc Sort'[Sort Order])"
I highlight Alt Loc (groups) in the field list then choose "Sort by Column" and choose Location_SortB
I get the error message:
I created a table to make sure that there are not more than one sort order number per item:
Any idea what I'm doing wrong?
5 Replies
The problem is the relationship. Because it is a one-to-many, that could result in a blank row being silently added to the one-side in the event that rows appear in the many side which don't exist in the one-side.
You can try deleting the relationship and instead of using RELATED use LOOKUPVALUE instead.
That might not work, again because of possible blank values in the lookup table. If that is the case you would need to create the sort order column directly on the target table, probably using SWITCH to define logic.
There are no blank values. I also broke the relationship to the sort table and the data table and used lookup to get the sort values. They come across correctly but when I try to apply the sort column to the data, I get a circular dependency error.
clarkpaul , LOOKUPVALUE() function will result in a circular dependency since you might have used Location column within your search field. Try fixing using RELATED() funtion or create a Sort Column at power query level by merging the queries.
Thanks,
Jai
Hi,
Share the download link of the PBI file and show the problem clearly.
