Forum Discussion

pubserv_22's avatar
pubserv_22
Frequent Visitor
3 years ago
Solved

IF statement for Time in DAX

I'm trying to create a column which returns a working shift time if a condition between two time columns is met in DAX (Direct Query). I know there are many other threads on this issue and I have followed them carefully but cannot get my conditional shift column to return successfully. 

 

The database columns are SCHEDULEDATE and SCHEDULEENDDATE which are in datetime format. I created new columns ScheduleTime and ScheduleEndTime which are simply the database columns but in time format. I assume I need this to make the IF statement using the TIME function to work. 

 

I have tried 2 different methods (seen in Shift & Shift1) and none return the correct result being "DayShift". Where am I going wrong here? Could it be my data formats?

 

Shift = IF(vFOLDERPROCESS[ScheduleTime] >= TIME(08,00,00) && vFOLDERPROCESS[ScheduleEndTime] < TIME(16,00,00), "DayShift", "Nightshift")
 
Shift1 = SWITCH(TRUE(), vFOLDERPROCESS[ScheduleTime] >= TIMEVALUE("08:00:00") && vFOLDERPROCESS[ScheduleEndTime] < TIMEVALUE("16:00:00"), "DayShift", "Nightshift")
 

 

 

Need Help
  • grantsamborn's avatar
    grantsamborn
    3 years ago

    If you change ScheduledTime and ScheduledEndTime to:

     

    ScheduleTime = MOD( PowerbiHelp[SCHEDULEDATE], 1)

ScheduleEndTime = MOD( PowerbiHelp[SCHEDULEENDDATE], 1 )

     

    This will ignore the date part of the datetime fields.

    Afterwards, you'll have to change the types back to Time and then your measure will work.

     

    Let me know if this helps.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi pubserv_22 ,

    According to your description, here are my steps you can follow as a solution.

    (1)Check that the [ScheduleTime] column and [ScheduleEndTime] columns are formatted as Time.

    (2)We can create  a measure.

    Shift = IF(SELECTEDVALUE('vFOLDERPROCESS'[ScheduleTime])>= TIME(08, 00, 00) && SELECTEDVALUE('vFOLDERPROCESS'[ScheduleEndTime]) < TIME(16,00,00), "DayShift", "Nightshift")

    (3)Then the result is as follows.

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

     

      • grantsamborn's avatar
        grantsamborn
        Icon for Solution Sage rankSolution Sage
        3 years ago

        The problem has to do with TIME() returning a datetime value on the date of 1899-12-30.  You can quickly verify this by creating a column or measure returning TIME(8,0,0).  dax.guide documentation for TIME also confirms this.

         

        Because your ScheduledTime and ScheduleEndTime are on a later date, the comparison won't work.

         

        I'm not really sure to how get around this with DirectQuery but with Import, I would create the 2 time columns in PowerQuery.

         

        Hope this points you in the right direction.