IF statement for Time in DAX

[pubserv_22]

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

[Anonymous]

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. 

[pubserv_22]

Hi Neeko,

Thank you very much for your reply. I have tried this option and unfortuantely it is still not returning my expected result. Here is the link to a simplified pbix file so that you can have a look. https://drive.google.com/drive/folders/1gbJBsC46foK-bK0jSTDhK-17CIqGKdmo?usp=sharing 

[grantsamborn · Accepted solution]

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.

[pubserv_22]

Thanks very much grantsamborn, this worked! I really appreciate it.

[grantsamborn]

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.