Help with IF AND OR statements nested

[jsaue003]

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 commas to finish it out... Any hints? 

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

[MFelix]

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]

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]

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]

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

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

@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"
)

[v-juanli-msft]

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

[MFelix]

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