Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

DAX if Function Help

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) ...
AlB's avatar
AlB
Community Champion
6 years ago

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  Datanaut

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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