Help with IF AND OR statements nested

Question

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?&nbsp;&nbsp;&nbsp;Column = if ([SDLC Phase]&lt;&gt;"Completed"&amp;&amp;[SDLC Phase]&lt;&gt;"Cancelled"&amp;&amp;[SDLC Phase]&lt;&gt;"On Hold"&amp;&amp;[Reportable]="Yes" &amp;&amp; 
(([Milestone Date 1]&lt;TODAY() &amp;&amp; [Milestone Date 1]&lt;&gt;"" &amp;&amp; [Milestone Health 1]&lt;&gt; "Off-Track")
|| 
([Milestone Date 2]&lt;TODAY() &amp;&amp; [Milestone Date 2]&lt;&gt;"" &amp;&amp; [Milestone Health 2]&lt;&gt; "Off-Track")
|| 
([Milestone Date 3]&lt;TODAY() &amp;&amp; [Milestone Date 3]&lt;&gt;"" &amp;&amp; [Milestone Health 3]&lt;&gt; "Off-Track")
|| 
([Milestone Date 4]&lt;TODAY() &amp;&amp; [Milestone Date 4]&lt;&gt;"" &amp;&amp; [Milestone Health 4]&lt;&gt; "Off-Track")
|| 
([Milestone Date 5]&lt;TODAY() &amp;&amp; [Milestone Date 5]&lt;&gt;"" &amp;&amp; [Milestone Health 5]&lt;&gt; "Off-Track")
|| 
([Milestone Date 6]&lt;TODAY() &amp;&amp; [Milestone Date 6]&lt;&gt;"" &amp;&amp; [Milestone Health 6]&lt;&gt; "Off-Track")
|| 
([Milestone Date 7]&lt;TODAY() &amp;&amp; [Milestone Date 7]&lt;&gt;"" &amp;&amp; [Milestone Health 7]&lt;&gt; "Off-Track"), “Yes”, “No”)&nbsp;&nbsp;

mfelix · Answer

Hi&nbsp;jsaue003&nbsp;,
&nbsp;
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:
&nbsp;
Column =
IF (
    [SDLC Phase] &lt;&gt; "Completed"
        &amp;&amp; [SDLC Phase] &lt;&gt; "Cancelled"
        &amp;&amp; [SDLC Phase] &lt;&gt; "On Hold"
        &amp;&amp; [Reportable] = "Yes"
        &amp;&amp; (
            [Milestone Date 1] &lt; TODAY ()
                &amp;&amp; [Milestone Date 1] &lt;&gt; ""
                &amp;&amp; [Milestone Health 1] &lt;&gt; "Off-Track"
        )
        || (
            [Milestone Date 2] &lt; TODAY ()
                &amp;&amp; [Milestone Date 2] &lt;&gt; ""
                &amp;&amp; [Milestone Health 2] &lt;&gt; "Off-Track"
        )
        || (
            [Milestone Date 3] &lt; TODAY ()
                &amp;&amp; [Milestone Date 3] &lt;&gt; ""
                &amp;&amp; [Milestone Health 3] &lt;&gt; "Off-Track"
        )
        || (
            [Milestone Date 4] &lt; TODAY ()
                &amp;&amp; [Milestone Date 4] &lt;&gt; ""
                &amp;&amp; [Milestone Health 4] &lt;&gt; "Off-Track"
        )
        || (
            [Milestone Date 5] &lt; TODAY ()
                &amp;&amp; [Milestone Date 5] &lt;&gt; ""
                &amp;&amp; [Milestone Health 5] &lt;&gt; "Off-Track"
        )
        || (
            [Milestone Date 6] &lt; TODAY ()
                &amp;&amp; [Milestone Date 6] &lt;&gt; ""
                &amp;&amp; [Milestone Health 6] &lt;&gt; "Off-Track"
        )
        || (
            [Milestone Date 7] &lt; TODAY ()
                &amp;&amp; [Milestone Date 7] &lt;&gt; ""
                &amp;&amp; [Milestone Health 7] &lt;&gt; "Off-Track"
        ),
    "Yes",
    "No"
)

Regards,
MFelix

mfelix · Answer

Hi&nbsp;jsaue003,
&nbsp;
Check if all your milestones date columns are formatted as date, believe that one of those fields is formated as text.
&nbsp;
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] &lt;&gt; "Completed"
        &amp;&amp; [SDLC Phase] &lt;&gt; "Cancelled"
        &amp;&amp; [SDLC Phase] &lt;&gt; "On Hold"
        &amp;&amp; [Reportable] = "Yes"
        &amp;&amp; (
            [Milestone Date 1] &lt; TODAY ()
                || [Milestone Date 1] &lt;&gt; BLANK()
                &amp;&amp; [Milestone Health 1] &lt;&gt; "Off-Track"
        )
        || (
            [Milestone Date 2] &lt; TODAY ()
                || [Milestone Date 2] &lt;&gt; BLANK()
                &amp;&amp; [Milestone Health 2] &lt;&gt; "Off-Track"
        )
        || (
            [Milestone Date 3] &lt; TODAY ()
                || [Milestone Date 3] &lt;&gt; BLANK()
                &amp;&amp; [Milestone Health 3] &lt;&gt; "Off-Track"
        )
        || (
            [Milestone Date 4] &lt; TODAY ()
                || [Milestone Date 4] &lt;&gt; BLANK()
                &amp;&amp; [Milestone Health 4] &lt;&gt; "Off-Track"
        )
        || (
            [Milestone Date 5] &lt; TODAY ()
                || [Milestone Date 5] &lt;&gt; BLANK()
                &amp;&amp; [Milestone Health 5] &lt;&gt; "Off-Track"
        )
        || (
            [Milestone Date 6] &lt; TODAY ()
                || [Milestone Date 6] &lt;&gt; BLANK()
                &amp;&amp; [Milestone Health 6] &lt;&gt; "Off-Track"
        )
        || (
            [Milestone Date 7] &lt; TODAY ()
                || [Milestone Date 7] &lt;&gt; BLANK()
                &amp;&amp; [Milestone Health 7] &lt;&gt; "Off-Track"
        ),
    "Yes",
    "No"
)
Regards,
MFelix
&nbsp;

jsaue003 · Answer

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

Hi&nbsp;jsaue003&nbsp;,
&nbsp;
try to change the milestone blanks to :
&nbsp;
&amp;&amp; [Milestone Date 3] = null
&amp;&amp; [Milestone Date 3] = blank()
&nbsp;
Test one of this formulas should work.
&nbsp;
Regards,
MFelix&nbsp;

jsaue003 · Answer

MFelix&nbsp;Not sure I understand. Where would I put the following line of code:&nbsp;&amp;&amp; [Milestone Date 3] = blank()Does this go at the end or something ?&nbsp;

Forum Discussion

Help with IF AND OR statements nested

8 Replies

Related content

Power Query Nested IF AND Statement

Nested IF/AND Statement

Multiple nested IF statements

Nested switch statements?

DAX Measure with Nested IF Statements