How to create a dax function to create calculated column based on time range

[rafiql09]

From the below date column a new calculated column 'shift_id, is needed

if the time is 8am to 9am time shift_id is 1

if the time is 7am to 8am shift_id is 5

if the time is 4pm to5pm shift_id is 10

within the same date, date would be filtered based on power bi slicer though.

etc...

thnaks

[MFelix]

Hi @rafiql09 

For this you need to add a new calculated column wiht a sintax similar to:

=if Time.Hour([Column1])>=7 and Time.Hour([Column1])<8 then 5 else if Time.Hour([Column1]) >=8 and Time.Hour([Column1]) < 9 then 1 else if Time.Hour([Column1]) >=16 and Time.Hour([Column1]) < 17 then 10 else null

[MFelix]

Hi @rafiql09

My bad the code I sent you was ffor Power Query. Please forgive me the lack of information but glad you already solved it. 

[rafiql09]

Thanks for helping me starting it .

I have made the changes and looks ok. But multiple IF giving me error!! Can you help please?

[FreemanZ · Accepted solution]

hi @rafiql09 

try like:

shift_id = 
VAR _hour = HOUR([TimeField])
RETURN
SWITCH(
    TRUE(),
    _hour>=8&&_hour<=9, 1,
    _hour>=7&&_hour<8, 5,
    _hour>=16&&_hour<=17, 10,
    999
)

[rafiql09]

Thanks @MFelix for the help. It raises syntax error which I could not fix.