Model Table Sort By Column Issue


#1

I have some dimension data which I have flattened into one table and I am trying to add sort by column into the model, but I keep get the error

Cannot sort KPA Description by SortOrderKPA because at least one value in KPA Description has multiple distinct values in SortOrderKPA. For example, you can sort [City] by [Region] because there is only one region for each city, but you cannot sort [Region] by [City] because there are multiple cities for each region.”

Sample table

I actually want to be able to sort by the KPA Description by the KPA Position, Outcome Description by the Outcome Position and the Activity Description by the Activity Position.

I’ve tried creating unique sort orders, but nothing seems to work. Any help would be appreciated.


#2

Even though you can’t see it, there will be a duplication of something in the column you want to sort by. It’s really as simple as that.

So some item in the KPA Decription column has multiple Sort Order KPA associated to it.

The way to check, is to in the front canvas create a measure:

DISTINCTCOUNT( KPA Description Column) and then place this in a table need to the SortOrderKPA column.

That really all I can advise. This pops up for me occasionally and this is how I solve it.