Sort By Calculated Column Error

[littlemojopuppy]

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

AgingCategorySortOrder = 
    SWITCH(ExcessInventory[AgingCategory],
        "CURRENT",0,
        "1 MONTH",1,
        "2 MONTHS",2,
        "3 MONTHS",3,
        "4 MONTHS",4,
        "5 MONTHS",5,
        "6 MONTHS",6,
        "7 MONTHS",7,
        "8 MONTHS",8,
        "9 MONTHS",9,
        "10 MONTHS",10,
        "11 MONTHS",11,
        "12 MONTHS",12,
        "1 YEAR",13,
        "2 YEARS OR MORE",14,
        99
    )

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!

[v-juanli-msft · Accepted solution]

Hi @littlemojopuppy

Here is a workaround

In query editor

1.duplicate the column[AgingCategory]

2.click on this duplicated column, split column by delimiter

3.create a conditional column

 

4.change the conditional column to type number, and remove other columns which not need finally.

 

Best Regards

Maggie

[littlemojopuppy]

Hi!  Know what...this works!  Thank you!!!

[AndreyBear]

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.

[kennedy311]

This helped me out of a similar jam as the OP. Thanks!

[LivioLanzo]

@littlemojopuppy

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

[littlemojopuppy]

That was the band aid solution that I went with, but I was hoping that there was something a little more elegant and dynamic than creating tables off to the side...