Showing Time range in order

[am_i_really]

Hi All, I'm having an issue with getting my data to sort correctly. I have the following switch statement I used to group the data into time ranges but it does not show in correct order. It wont allow me to sort the column either

TimeGroup = SWITCH(
    TRUE(),
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(00,00,00), SF_YTD_Master[ORDER_TIME] < TIME(03,00,00)), "12-3 am",
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(03,00,00), SF_YTD_Master[ORDER_TIME] < TIME(06,00,00)), "3-6 am",
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(06,00,00), SF_YTD_Master[ORDER_TIME] < TIME(09,00,00)), "6-9 am",
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(09,00,00), SF_YTD_Master[ORDER_TIME] < TIME(12,00,00)), "9-12 pm",
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(12,00,00), SF_YTD_Master[ORDER_TIME] < TIME(15,00,00)), "12-3 pm",
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(15,00,00), SF_YTD_Master[ORDER_TIME] < TIME(18,00,00)), "3-6 pm",
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(18,00,00), SF_YTD_Master[ORDER_TIME] < TIME(21,00,00)), "6-9 pm",
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(21,00,00), SF_YTD_Master[ORDER_TIME] <= TIME(23,59,59)), "9-12 am"
)

Thanks in advance!

[am_i_really]

That worked, thank you!

[Arul · Accepted solution]

@am_i_really ,

You have sort this column at model level. Try the below code to create a calculated column and then use sort by column option at model level,

TimeGroup Sort = SWITCH(
    TRUE(),
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(00,00,00), SF_YTD_Master[ORDER_TIME] < TIME(03,00,00)), 1,
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(03,00,00), SF_YTD_Master[ORDER_TIME] < TIME(06,00,00)), 2,
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(06,00,00), SF_YTD_Master[ORDER_TIME] < TIME(09,00,00)), 3,
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(09,00,00), SF_YTD_Master[ORDER_TIME] < TIME(12,00,00)), 4,
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(12,00,00), SF_YTD_Master[ORDER_TIME] < TIME(15,00,00)), 5,
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(15,00,00), SF_YTD_Master[ORDER_TIME] < TIME(18,00,00)), 6,
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(18,00,00), SF_YTD_Master[ORDER_TIME] < TIME(21,00,00)), 7,
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(21,00,00), SF_YTD_Master[ORDER_TIME] <= TIME(23,59,59)), 8
)

Then select the time group column and then use time group sort in sort by column option,

Thanks,

Arul