Sort order error message when trying to sort by another column

[Fusilier]

I'm trying to sort a 100% stacked bar chart in a specific order

I've created a calculated column in the data table with the sort order assigned to the values I want to sort by:

Answer Sort Order value =

IF('Combined Table'[Answer]="Very safe",1,

IF('Combined Table'[Answer]="Fairly safe",2,

IF('Combined Table'[Answer]="Neither safe nor unsafe",3,

IF('Combined Table'[Answer]="Fairly unsafe",4,

IF('Combined Table'[Answer]="Very unsafe",5)))))

But when I try and sort my 'Answer' column by that column I get this error message:

I'm sure I've managed to do this easily enough in the past. What am I missing?

[BIswajit_Das · Accepted solution]

Hello @Fusilier 

In your DAX you are using [Answer] column to apply conditions and return required result.
That's why you can not order the created column by [Answer] column cause you are using [Answer] column in order to create the new column.
So if you want to order the column first add a column with [Answer] column data and name it as your please (e.g. [Answerx]), then order the created [Answer Sort Order value] by [Answerx] and it'll work.
Remember not to duplicate direct [Answer] or the same error will come try to create/ add the column in tranfrom data section.
Thanks & Regards ...

[BIswajit_Das · Accepted solution]

Hello @Fusilier 

In your DAX you are using [Answer] column to apply conditions and return required result.
That's why you can not order the created column by [Answer] column cause you are using [Answer] column in order to create the new column.
So if you want to order the column first add a column with [Answer] column data and name it as your please (e.g. [Answerx]), then order the created [Answer Sort Order value] by [Answerx] and it'll work.
Remember not to duplicate direct [Answer] or the same error will come try to create/ add the column in tranfrom data section.
Thanks & Regards ...

[Fusilier]

Thanks for your help. Unfortunately I don't understand your solution.

Why do seemingly simple things in Power BI turn out to be so complicated to achieve!

[v-yaningy-msft · Accepted solution]

Hi, @Fusilier 

Thanks for BIswajit_Das positive and useful reply. What he means is to copy an Answered column (in PQ) called Answer.1, use the Answer.1 column to generate the Answer Sort Order value column, and then sort the Answer column. In fact, the situation you describe is by design, and it leads to circular dependencies.

You can submit an idea for here to help us improve PBI and the product team will take note of such a need.

Related Link: New tab (powerbi.com)

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum