Error when using a column to sort another column

[clarkpaul]

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?

[Ashish_Mathur]

Hi,

Share the download link of the PBI file and show the problem clearly.

[clarkpaul]

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.

[Jai-Rathinavel]

@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

[johnt75]

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.

[MFelix]

Hi @clarkpaul ,

Try to check if there are any blanks on that new Location_SortB column and you try to do a sort by and there are blank values it does not accept to do the sorting.