Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Nested IF Statement Ignoring Blanks

Hi,

 

Im looking for some help creating a new column to distinguish wether an Appointment was Early, Late or In Target. 

 

I have 3 fields that are going to be used APPOINTMENT ACTUAL_START_DATE, APPOINTMENT_WINDOW_START_DATE and APPOINTMENT_WINDOW_END_DATE. 

 

Im im currently having issues ignoring the blanks in the APPOINTMENT_ACTUAL_START_DATE. Heres what i want to acheive

 

APPTCTGY = IF(AND(APPT[APPOINTMENT_ACTUAL_START_DATE < APPOINTMENT_WINDOW_START_DATE, "EARLY"),
                      IF(AND(APPT[APPOINTMENT_ACTUAL_START_DATE >APPOINTMENT_WINDOW_START_DATE, "LATE"),
                       IF(AND(APPT[APPOINTMENT_ACTUAL_START_DATE >= APPOINTMENT_WINDOW_START_DATE   AND   < =                                     APPOINTMENT_WINDOW_END_DATE , "IN TARGET"))))   
 
 
Any ideas how i can run a statement similar to this ignoring the Blanks in the APPOINTMENT_ACTUAL_START_DATE as this is returning as a "EARLY" when it shouldnt be included.
 
 
Thanks
 
Alex
General Comment
Need Help
Tips and Tricks
  • MartynRamsden's avatar
    MartynRamsden
    6 years ago

    Hi Anonymous 

     

    If you're adding this as a calculated column, you don't need all of the MAX functions. They are essentially calculating the max value for that entire column - not what you're after!

     

    I always favour the SWITCH function rather than nested IF statements as I find it easier to read.

     

    Try adding a calculated column with the expression below:

    APPTCTGY =
SWITCH(
    TRUE(),
    ISBLANK( FACT_Appt[APPOINTMENT ACTUAL_START_DATE] ), "EARLY",
    FACT_Appt[APPOINTMENT ACTUAL_START_DATE] < FACT_Appt[APPOINTMENT_WINDOW_START_DATE ], "EARLY",
    FACT_Appt[APPOINTMENT ACTUAL_START_DATE] > FACT_Appt[APPOINTMENT_WINDOW_END_DATE], "LATE",
    FACT_Appt[APPOINTMENT ACTUAL_START_DATE] >= FACT_Appt[APPOINTMENT_WINDOW_START_DATE]
        && FACT_Appt[APPOINTMENT ACTUAL_START_DATE]
            <= MAX(FACT_Appt[APPOINTMENT_WINDOW_END_DATE] ), "IN TARGET"
)

     

    Best regards,
    Martyn

     

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    You can use Switch True and use isblank to check for blank

    Example

    Switch (true(),

    condition, action,

    condition, action,

    condition, action,

    else action)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks. What logiv would you use within the switch to ignore the null values in the ACTUAL_APPOINTMENT)START_DATE field.

       

      Thanks

       

      Alex

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Switch( true(),

        isblank(APPOINTMENT_ACTUAL_START_DATE ), "Early",

        ....

        )

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    You could create a measure like this:

    APPTCTGY =
IF (
    ISBLANK ( MAX ( 'table'[ APPOINTMENT ACTUAL_START_DATE] ) ),
    "EARLY",
    IF (
        MAX ( 'table'[ APPOINTMENT ACTUAL_START_DATE] )
            < MAX ( 'table'[APPOINTMENT_WINDOW_START_DATE ] ),
        "EARLY",
        IF (
            MAX ( 'table'[ APPOINTMENT ACTUAL_START_DATE] )
                > MAX ( 'table'[APPOINTMENT_WINDOW_END_DATE] ),
            "LATE",
            IF (
                MAX ( 'table'[ APPOINTMENT ACTUAL_START_DATE] )
                    >= MAX ( 'table'[APPOINTMENT_WINDOW_START_DATE ] )
                    && MAX ( 'table'[ APPOINTMENT ACTUAL_START_DATE] )
                        <= MAX ( 'table'[APPOINTMENT_WINDOW_END_DATE] ),
                "IN TARGET"
            )
        )
    )
)

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

       

      Thanks for your reply.

       

      I have followed your code in creating a new column and when the following is applied its returning all values as "EARLY"

       

      • MartynRamsden's avatar
        MartynRamsden
        Solution Sage
        6 years ago

        Hi Anonymous 

         

        If you're adding this as a calculated column, you don't need all of the MAX functions. They are essentially calculating the max value for that entire column - not what you're after!

         

        I always favour the SWITCH function rather than nested IF statements as I find it easier to read.

         

        Try adding a calculated column with the expression below:

        APPTCTGY =
SWITCH(
    TRUE(),
    ISBLANK( FACT_Appt[APPOINTMENT ACTUAL_START_DATE] ), "EARLY",
    FACT_Appt[APPOINTMENT ACTUAL_START_DATE] < FACT_Appt[APPOINTMENT_WINDOW_START_DATE ], "EARLY",
    FACT_Appt[APPOINTMENT ACTUAL_START_DATE] > FACT_Appt[APPOINTMENT_WINDOW_END_DATE], "LATE",
    FACT_Appt[APPOINTMENT ACTUAL_START_DATE] >= FACT_Appt[APPOINTMENT_WINDOW_START_DATE]
        && FACT_Appt[APPOINTMENT ACTUAL_START_DATE]
            <= MAX(FACT_Appt[APPOINTMENT_WINDOW_END_DATE] ), "IN TARGET"
)

         

        Best regards,
        Martyn

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    could you please share sample dataset and expected output.

     

    Why there are to many "AND" in your formula?

     

    Thanks,

    Pravin