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 is 10

 

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

etc...

 

thnaks

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
)

5 Replies

  • 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 MFelix for the help. It raises syntax error which I could not fix.

       

       

    • 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
)

    • MFelix's avatar
      MFelix
      Super User
      3 years ago

      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.