Sort by calculated column

[Anonymous]

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

[Apanelo]

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

[mmace1]

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]

@mmace1 Thanks for the reply!

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

[mmace1]

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]

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

[mmace1]

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?