Forum Discussion

jsaue003's avatar
jsaue003
Frequent Visitor
7 years ago

Help with IF AND OR statements nested

My goal is to create a column that will check if any milestone date is less than today, not empty, and not off track. I have it almost 100% correct, but I can't figure out the parentheses and the com...
Need Help
MFelix's avatar
MFelix
Super User
7 years ago

Hi jsaue003 ,

 

try to change the milestone blanks to :

 

&& [Milestone Date 3] = null

&& [Milestone Date 3] = blank()

 

Test one of this formulas should work.

 

Regards,

MFelix 

jsaue003's avatar
jsaue003
Frequent Visitor
7 years ago

MFelix Not sure I understand. Where would I put the following line of code:

 

&& [Milestone Date 3] = blank()

Does this go at the end or something ? 

  • ChrisMendoza's avatar
    ChrisMendoza
    Resident Rockstar
    7 years ago

    jsaue003 -

     

    I believe the intention is replace all of your:

     

    && [Milestone Date 3] = ""

    with:

    && [Milestone Date 3] = BLANK()

    I am guessing because "" is an empty string which is causing your error.

    • jsaue003's avatar
      jsaue003
      Frequent Visitor
      7 years ago

      ChrisMendoza I did try that, but it doesn't seem to change much. I get the error: DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values.

       

      As can be seen below, I did replace all of the date comparisons with 'Blank'.

       

      Missing P2G = 
IF (
    [SDLC Phase] <> "Completed"
        && [SDLC Phase] <> "Cancelled"
        && [SDLC Phase] <> "On Hold"
        && [Reportable] = "Yes"
        && (
            [Milestone Date 1] < TODAY ()
                && [Milestone Date 1] <> BLANK()
                && [Milestone Health 1] <> "Off-Track"
        )
        || (
            [Milestone Date 2] < TODAY ()
                && [Milestone Date 2] <> BLANK()
                && [Milestone Health 2] <> "Off-Track"
        )
        || (
            [Milestone Date 3] < TODAY ()
                && [Milestone Date 3] <> BLANK()
                && [Milestone Health 3] <> "Off-Track"
        )
        || (
            [Milestone Date 4] < TODAY ()
                && [Milestone Date 4] <> BLANK()
                && [Milestone Health 4] <> "Off-Track"
        )
        || (
            [Milestone Date 5] < TODAY ()
                && [Milestone Date 5] <> BLANK()
                && [Milestone Health 5] <> "Off-Track"
        )
        || (
            [Milestone Date 6] < TODAY ()
                && [Milestone Date 6] <> BLANK()
                && [Milestone Health 6] <> "Off-Track"
        )
        || (
            [Milestone Date 7] < TODAY ()
                && [Milestone Date 7] <> BLANK()
                && [Milestone Health 7] <> "Off-Track"
        ),
    "Yes",
    "No"
)
      • MFelix's avatar
        MFelix
        Super User
        7 years ago

        Hi jsaue003,

         

        Check if all your milestones date columns are formatted as date, believe that one of those fields is formated as text.

         

        One other thing making the look at your data yuou have an incorrect parameter on the comparision of your date columns you are making if milestone date lower than today and if milestone equals blanks this will get the incorrect result you need to do a or change your measure to:

        Missing P2G = 
IF (
    [SDLC Phase] <> "Completed"
        && [SDLC Phase] <> "Cancelled"
        && [SDLC Phase] <> "On Hold"
        && [Reportable] = "Yes"
        && (
            [Milestone Date 1] < TODAY ()
                || [Milestone Date 1] <> BLANK()
                && [Milestone Health 1] <> "Off-Track"
        )
        || (
            [Milestone Date 2] < TODAY ()
                || [Milestone Date 2] <> BLANK()
                && [Milestone Health 2] <> "Off-Track"
        )
        || (
            [Milestone Date 3] < TODAY ()
                || [Milestone Date 3] <> BLANK()
                && [Milestone Health 3] <> "Off-Track"
        )
        || (
            [Milestone Date 4] < TODAY ()
                || [Milestone Date 4] <> BLANK()
                && [Milestone Health 4] <> "Off-Track"
        )
        || (
            [Milestone Date 5] < TODAY ()
                || [Milestone Date 5] <> BLANK()
                && [Milestone Health 5] <> "Off-Track"
        )
        || (
            [Milestone Date 6] < TODAY ()
                || [Milestone Date 6] <> BLANK()
                && [Milestone Health 6] <> "Off-Track"
        )
        || (
            [Milestone Date 7] < TODAY ()
                || [Milestone Date 7] <> BLANK()
                && [Milestone Health 7] <> "Off-Track"
        ),
    "Yes",
    "No"
)

        Regards,

        MFelix