Help With SWITCH Function - DAX Check Request

[Anonymous]

My goal is to take hours in the day and put them into buckets. The calculated column output is all NULL values. I can't seem to get the DAX correct. Any advice you can provide on where I am going wrong is greatly appreciated. 

8hr Time Band =

SWITCH(

TRUE(),

AND(

DATEDIFF( 'Time'[HourNum], 'Time'[HourNum], HOUR ) >= 1,

(DATEDIFF( 'Time'[HourNum], 'Time'[HourNum], HOUR ) <= 😎 ) , "1:00 AM - 8:00 AM",

AND(

DATEDIFF( 'Time'[HourNum], 'Time'[HourNum], HOUR ) >= 9,

(DATEDIFF( 'Time'[HourNum], 'Time'[HourNum], HOUR ) <= 16) ) , "9:00 AM - 4:00 PM",

AND(

DATEDIFF( 'Time'[HourNum], 'Time'[HourNum], HOUR ) >= 17,

(DATEDIFF( 'Time'[HourNum], 'Time'[HourNum], HOUR ) <= 0) ), "5:00 PM - 12:00 AM"

)

[Anonymous · Accepted solution]

I was able to find the solution to my own problem by changing the ordering of the hour inputs. It is best to start with the midnight hour (0) and work up from there. 

8hr Time Band =

SWITCH(

TRUE(),

AND(

DATEDIFF( 'Time'[HourNum], 'Time'[HourNum], HOUR ) >= 0,

(DATEDIFF( 'Time'[HourNum], 'Time'[HourNum], HOUR ) <= 7) ) , "12:00 AM - 7:00 AM",

AND(

DATEDIFF( 'Time'[HourNum], 'Time'[HourNum], HOUR ) >= 8,

(DATEDIFF( 'Time'[HourNum], 'Time'[HourNum], HOUR ) <= 15) ) , "8:00 AM - 3:00 PM",

AND(

DATEDIFF( 'Time'[HourNum], 'Time'[HourNum], HOUR ) >= 16,

(DATEDIFF( 'Time'[HourNum], 'Time'[HourNum], HOUR ) <= 23) ), "4:00 PM - 11:00 PM"

)