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, " <...
Need Help
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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

       

      Im so sorry but I still do not have a solution mmace1 

      • mmace1's avatar
        mmace1
        Impactful Individual
        5 years ago

        So your last entry is the literal number 6, while the rest are strings - i'e "5- Target...bla bla".  I think that'd throw an error once you get there. 

        I have an example file here: 
        https://www.dropbox.com/s/729i58ovutw1fvv/Example.pbix?dl=0

        I think probably your issue is that you're switch statement on your sort column, is still pointing to the Measure, instead of an actual column.  I think you'd need a column with [Avg. Note to delivery h], then reference that column for both your SWITCH column , and your SORT column. 

        I don't suppose you can upload your file?