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,...
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

     

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

      Thats worked a treat Martyn thanks very much for your help.  This switch funstion is really adaptable and i shall be able to use this in many other areas of my model.

       

      Thanks very much

       

      Alex