topic Re: DAX if Function Help in DAX Commands and Tips

DAX if Function Help

Anonymous — Fri, 13 Sep 2019 03:41:13 GMT

Hi,

I have a formula that does not implement my logic correctly. I have 3 columns with Date/Time values:

Job booking datetime (no blanks)
Attempted Acceptance Datetime (including blank cells)
Acceptance Datetime (including blank cells)

The logic is:

if Job Booking DateTime < 12 midday AND Attempted Acceptance Scan DateTime && Acceptance DateTime occur on the SAME DAY as Job Booking DateTime, then output "YES", else "NO"

Here is current formula that I need tweaked:

VAR Time1_ = HOUR(FF_August_Dataset[JOB_BOOKING_DATETIME])
VAR Time2_ = HOUR(FF_August_Dataset[ATTEMPTED_ACCEPTANCE_DATETIME])
VAR Time3_ = HOUR(FF_August_Dataset[ACCEPTANCE_DATETIME])
VAR Date1_ = INT(FF_August_Dataset[JOB_BOOKING_DATETIME])
VAR Date2_ = INT(FF_August_Dataset[ATTEMPTED_ACCEPTANCE_DATETIME])
VAR Date3_ = INT(FF_August_Dataset[ACCEPTANCE_DATETIME])
VAR SameDate_ = (Date1_ = Date2_) && (Date2_ = Date3_)
RETURN
IF( SameDate_ && Time1_ < 12 && Time2_ < 12 && Time3_ < 12, "Yes", "No")

Appreciate any help, thanks.

Re: DAX if Function Help

AlB — Fri, 13 Sep 2019 08:24:53 GMT

You can just drop the conditions (and VARs) you don't need:

VAR Time1_ = HOUR(FF_August_Dataset[JOB_BOOKING_DATETIME])
VAR Date1_ = INT(FF_August_Dataset[JOB_BOOKING_DATETIME])
VAR Date2_ = INT(FF_August_Dataset[ATTEMPTED_ACCEPTANCE_DATETIME])
VAR Date3_ = INT(FF_August_Dataset[ACCEPTANCE_DATETIME])
VAR SameDate_ = (Date1_ = Date2_) && (Date2_ = Date3_)
RETURN
IF( SameDate_ && Time1_ < 12 , "Yes", "No")

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers

Re: DAX if Function Help

Anonymous — Fri, 13 Sep 2019 10:33:34 GMT

AlB thank you for responding. I just wasn't sure which conditions / VARS to change but I'll give it a try.

Re: DAX if Function Help

Anonymous — Fri, 13 Sep 2019 10:49:28 GMT

AlB my new logic states that Time2 and Time3 have to be on the SAME date/day as Time1 so instead of <12, do I insert <23:59 or <0:00?

IF( SameDate_ && Time1_ < 12 && Time2_ < 12 && Time3_ < 12, "Yes", "No")

Re: DAX if Function Help

Anonymous — Fri, 13 Sep 2019 14:03:36 GMT

AlB I changed the formula to reflect the new logic but I'm skeptical of the output as per screenshot below which is heavily skewed based on 72k rows of data. Is my formula correct?

Here is link to full dataset if you are able to replicate from your end.

=VAR Time1_ = HOUR(FF_August_Dataset[JOB_BOOKING_DATETIME])
VAR Time2_ = HOUR(FF_August_Dataset[ATTEMPTED_ACCEPTANCE_DATETIME])
VAR Time3_ = HOUR(FF_August_Dataset[ACCEPTANCE_DATETIME])
VAR Date1_ = INT(FF_August_Dataset[JOB_BOOKING_DATETIME])
VAR Date2_ = INT(FF_August_Dataset[ATTEMPTED_ACCEPTANCE_DATETIME])
VAR Date3_ = INT(FF_August_Dataset[ACCEPTANCE_DATETIME])
VAR SameDate_ = (Date1_ = Date2_) && (Date2_ = Date3_)
RETURN
IF( SameDate_ && Time1_ < 12 && Time2_ < 24 && Time3_ < 24, "Yes", "No")