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

     

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi jsaue003 

    Is this problem sloved?

    If this post helps, then please consider Accept MFelix 's answer as the solution to help the other members find it more quickly.

     

    If not, please let me know.

     

    Best Regards

    Maggie