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 ,

 

you issue is with the parentheses that are double on the second line of you code one is additional, your calculation should look like this:

 

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

Regards,

MFelix

jsaue003's avatar
jsaue003
Frequent Visitor
7 years ago

Hello MFelix , and thank you for the assistance. I tried this, and it worked.... But now the issue is that it claims, 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.

 

Any ideas how I could troubleshoot this issue? I changed all milestone date columns to type date, but it didn't seem to fix the issue. As far as can be seen, that is the only field(s) being compared to 'TODAY()'

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