Sort Order not working - There can't be more than one value when there is only one value!

[DakWiser]

I have a lot of evaluation data (table name is [*All Years - Evaluation Data]) and one of the questions/columns in my dataset is a Customer Satisfaction score (column name [I was satisfied with the quality of this session]) with the following categories: Strongly agree, Agree, Disagree, Strongly disagree, and NULL. I am trying to sort the legend in a 100% stacked chart and having errors I don't understand. I have been successful in sorting ledgends before and don't know why this time it's not working.

I created a sort order table (table name is [zCSAT Scale Order]) that is connected to [*All Years - Evaluation Data] with a one to many relationship ([CSAT] --> [I was satisfied with the quality of this session]). Then I created a custom column in DAX using the code "Order CSAT = RELATED(zCSAT Scale Order[Order]".

zCSAT Scale Order table:

One to many relationship:

Custom Column in Evaluation Data table:

I even see that the custom column worked when I look at the data view of [*All Years - Evaluation Data]

However, when I try to sort [I was satisfied with the quality of this session] by that custom column it gives me an error saying there can't be more than one value... but there isn't! I have done this successfully for 2 other tables in my dataset using the same process and I am so confused why this isn't working - can anyone help?

My hypothesis is that I have sort order tables set up and connected for the other columns in that same table [*All Years- Evaluation Data] that have the same scale & sort orders, but are connected directly just to the columns where it applies. Would that be throwing it off? And if so, how do I sort these scales for all the evaluation questions that have this 4-point likert scale?

[DakWiser]

1. Did you double check for any leading spaces or case sensivity mismatches between your maintable and sort order table? - Yes! I went to the raw data and copied the categories exactly how they appear in the data, so I ruled that out as an issue

2. Do you have nulls or blanks in this column [I was satisfied with the quality of this session]? - I did, but I replaced those with "NULL" and have "NULL" as a category in my sort order reference table

I have tried everything and it is not working... ugh so frustrating!

[Deelip]

hi @DakWiser 

Did you double check for any leading spaces or case sensivity mismatches between your maintable and sort order table? Do you have nulls or blanks in this column [I was satisfied with the quality of this session]? 

The sort order table needs to have the same exact category set as per this column [I was satisfied with the quality of this session].