cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Sort By Calculated Column Error

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

5 REPLIES 5
LivioLanzo Super Contributor
Super Contributor

Re: Sort By Calculated Column Error

@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!  

Re: Sort By Calculated Column Error

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

Highlighted
Community Support Team
Community Support Team

Re: Sort By Calculated Column Error

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

Re: Sort By Calculated Column Error

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

AndreyBear Frequent Visitor
Frequent Visitor

Re: Sort By Calculated Column Error

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.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)