Hello! Have a question about using a calculated column to sort another column. I have a column of basically aging categories that don't sort in the proper order. So I created a calculated column as follows
Calculated column produces the intended results (AgingCategory selected and the AgingCategorySortOrder column at the end)
Except...when I go to modeling and attempt to set the Sort By Column for AgingCategory to the newly created column I get the following error...
I've tried to work around the problem by creating the column in the Query Editor as a Conditional Column, but that failed...too many categories. I tried creating another table to SUMMARIZE the different values for AgingCategory and put the column in there and then use RELATED to pull back into original table, but that failed (not surprisingly...it was just pushing out the reference).
Could someone please offer some advice on how to work this? Thank you!
PS - I don't understand why this should generate an error at all!
There is a circualr reference problem. You could create a mapping table and then merge it to the main table (Join operation) using power query and use the Sort By Column features once it is in the data model
Did I answer your question correctly? Mark my answer as a solution!
Wow, that's a clunker of a solution. Here is a better one:
1. Your switch statement is on point, BUT you cannot sort the origin column by derived colum,... so
2. Duplicate the origin column, sort that duplicated colum by that switch statement result. Now you are NOT sorting origin by result, and DAX is ok with that.
3. Use that duplicated colum as the label on your visuals. That is the only reason you need it to be sorted. I usually prefix those with "_out" in all my models. Invariably, you end up needing to specially format/logic the displayed label anyway.
The original proposed solution does essentially that, but substitues SWITCH for the conditional formating using GUI. That is very hard to maintain and read.