cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
littlemojopuppy
Super User
Super User

Sort By Calculated Column Error

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)
Capture.PNG


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...
Capture.PNG

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!

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @littlemojopuppy

Here is a workaround

In query editor

1.duplicate the column[AgingCategory]

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

11.png

 

3.create a conditional column

 

9.png 

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

 

10.png 

 

Best Regards

Maggie

 

View solution in original post

6 REPLIES 6
v-juanli-msft
Community Support
Community Support

Hi @littlemojopuppy

Here is a workaround

In query editor

1.duplicate the column[AgingCategory]

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

11.png

 

3.create a conditional column

 

9.png 

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

 

10.png 

 

Best Regards

Maggie

 

View solution in original post

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

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.

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

LivioLanzo
Solution Sage
Solution Sage

@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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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...

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors