Forum Discussion

rafiql09's avatar
rafiql09
Frequent Visitor
3 years ago
Solved

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

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...
Need Help
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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
)

MFelix's avatar
MFelix
Super User
3 years ago

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
rafiql09's avatar
rafiql09
Frequent Visitor
3 years ago

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's avatar
    FreemanZ
    Super User
    3 years ago

    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
)