Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Sort by calculated column

Hi Power BI Community! 


I have this calculated column: 

 

Target Data HQ =
var _diff = TrackET[Aver. Note to delivery h]
return
Switch ( True() ,
_diff <= 42, " < 42 h" ,
_diff <= 48, " < 48 h - Target Unscheduled Tool Down" ,
_diff <= 72, " < 72 h ",
_diff <= 96, "< 96 h - Target Scheduled Tool Down",
_diff <= 196, "< 196 h - Target Tool up/Replenishment",
" > 196 h"
)

I can not creat a custom sort oder because it is a calculated column.
 


How can I still sort the right way? < 42 h, < 48 h, < 72 h, <96 h, < 196, >196

Need Help

6 Replies

Replies have been turned off for this discussion
  • mmace1's avatar
    mmace1
    Impactful Individual
    5 years ago

    You can't sort a calculated column, by the column it's derived from. 

    But you could make a second calculated column, and sort your original calculated column by that. 

     

    Target Data HQ Sort =
var _diff = TrackET[Aver. Note to delivery h]
return
Switch ( True() ,
_diff <= 42, 1 ,
_diff <= 48, 2 - Target Unscheduled Tool Down" ,
_diff <= 72, 3 ,
_diff <= 96, 4- Target Scheduled Tool Down",
_diff <= 196, 5- Target Tool up/Replenishment",
6
)

     


    Then go to [Column Tools]  > [Sort By Column] and sort your original calculated column, by that new column.  

    And probably hide that 'sort' column from your data model, so as to not confuse anyone. 


    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      mmace1 Thanks for the reply!

      But I have this error because of the second calculated column:

      • mmace1's avatar
        mmace1
        Impactful Individual
        5 years ago

        Is [Target Data HQ Sort] defined by [Target Data HQ]?  Because you can't do that, or it'll throw the circular dependency error when you try to sort. 

        [Target Data HQ Sort] needs to be defined by a 3rd column that's just 

         

         

         

        TrackET[Aver. Note to delivery h]

         


        Example I just made locally:  The Switch column is defined by Original, and the Swithc Sort Column is also defined by Original. Then I can sort the Switch Column, By Switch Sort Column, fine without an error. 

  • Apanelo's avatar
    Apanelo
    Frequent Visitor
    2 years ago

    Recreate your calculated column as another column but instead of having the expected value you want in your SWITCH, you just put integers 1 to 5. See formula below.

    Target Data HQ =
    var _diff = TrackET[Aver. Note to delivery h]
    return
    Switch ( True() ,
    _diff <= 42, 1 ,
    _diff <= 48, 2,
    _diff <= 72, 3,
    _diff <= 96, 4,
    _diff <= 196, 5,
    6
    )

    After creating this new column, you can now apply Sort by Columns to your original calculated column